互联网时代，竞争越演越烈，说到底最值钱的或者说最具潜在价值应该就是信息了。那么作为一个Java程序员，在对数据操作原理和数据库的功力上将会是衡量一个Java程序员优劣的标准。那么，不闲扯了。在这片文章就简单的谈谈Java数据库连接（Java Database Connectivity）即：JDBC。
当然我不是要说些什么Java的Interface，我只是想说对于一个常常在使用java.sql.*;中接口、类的童鞋，如果总是直接去使用PreparedStatement、CallableStatement、Connection等标准的东西直接写一堆数据库操作代码，这样是很累的，好在Java的面向对象给我们提供了便利，对于常见的数据操作我们可以对那些本类、接口进行二次封装，以满足我们日常开发的需求。说到这，也许大家就会想到MyBatis、Hibernate、JDBC等等东西，这些开源的框架确实很牛，你可以获取到它的源代码，然后下功夫去读它，分析它，然后不停的测试，当然我想大部分人都不会这么去做的，因为到处都是现成的案例，搞个jar包，build path一下，ok，设置一下XML，按流程把代码一塞，项目里边跑起来视乎很库，也很简单。但是别忘了，你是一个“程序员”，不是“程序猿”、“农民工”。框架很牛，很多一般性问题它帮你解决的很好，可是这个时候我们似乎应该想想，如果我来做这个框架的开发，我是不是可以写得出来呢？我能做到怎样？那些源码的贡献者们能把这些框架做到这种程度是不是非常牛？好了，想必大家心里都有答案。
那么，现在我们就来就来写一个自己的简单的数据操作工具类SQLHelper，当然在实现之前需要说明一下：
1. 这只是一个jdbc的基本常用类的封装，为的是可以我们日常的简单单点开发数据连接开发服务。
2. 在这个SQLHelper类里面我没有考虑多线程的问题，因为为了简单。
3. 在你使用SQLHelper这个类是的时候还需要注意一点，它永远只有一个对数据库的连接。
4. 所有的存储过程的调用方法都是建立在数据库中写好过程的前提下。
5. 没有对分页作封装，但是分页的部分可以利用SQLHelper.executeQuery方法做进一步的简单封装，当然之所以没有做封装也是为了简单。
6. 测试中我连接的是Oracle 11g，可根据具体情况自行重新设置db_info.properties文件参数。
7. 别把这个SQLHelper想的有多么强大，但是对于简单的单链接操作，足够。
那么，现在就来看一看SQLHelper代码的封装（代码展示完后将在后边进行简要讲解）：
package cn.jasber.sql.util;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* This is a simple CRUD tool, I just packaging the Java basic package
* java.sql.* , java.io.FileInputStream; and java.util.Properties ordinarily;
* This encapsulation just can makes the basically and commonly satisfy when I
* have to operate the database. And Something you must attention on it,which
* the tool class SQLHelper always provides a connection to database only, and
* this code have some defects exist more. Ifyou want get more connections you
* can refactor the code.
*
* @author Jasber-Yon
* @version 2014-12-4
*
*/
public class SQLHelper {
private static Connection conn = null;
private static PreparedStatement pstat = null;
private static ResultSet rs = null;
private static CallableStatement cstat = null;
private static String driver = null;
private static String url = null;
private static String user = null;
private static String password = null;
private static Properties prop = null;
private static FileInputStream fis = null;
/**
* The driver and the Database configuration informations just to load one
* time.
*/
static {
try {
// get the Oracle connection information from the db_info.properties
// file
prop = new Properties();
fis = new FileInputStream("db_info.properties");
prop.load(fis);
driver = prop.getProperty("driver");
url = prop.getProperty("url");
user = prop.getProperty("user");
password = prop.getProperty("password");
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
// The errors happening there will seriously,so we make the program
// exit directly.
System.exit(-1);
} finally {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
fis = null; // Benefit to gc.
}
}
private static Connection getConnection() {
try {
conn = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static void close(ResultSet rs, Statement pstat, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
rs = null;
}
if (pstat != null) {
try {
pstat.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
pstat = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
conn = null;
}
}
/**
* This method can execute DML operations, and you do just one DML sentence.
*
* @param sql
* When you use this method, you ought to do like this: String
* sql =
* "update table_zzzzzname set columName=? where otherColumName=?"
* ; the '?' represent a sign in interface PreparedStatement, and
* the '?' begin to 1...
* @param params
* This parameters saves the values that you want to exchange the
* '?',it should be like: {"Jasber","Neo","exchange"...};
*/
public static void executeUpdate(String sql, String[] params) {
conn = SQLHelper.getConnection();
try {
pstat = conn.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
pstat.setString(i + 1, params[i]);
}
}
pstat.executeUpdate();
} catch (SQLException e) {
// if the DML sentence not succeed
throw new RuntimeException(e.getMessage());
} finally {
close(null, pstat, conn);
}
}
/**
* When you need to execute more than one DML sentence, you ought to use
* this method that includes the affairs dispose.
*
* @param sql
* The sql[] that you can reference more than one DML sentence.
* @param params
*/
public static void executeUpdate(String[] sql, String[][] params) {
conn = SQLHelper.getConnection();
try {
conn.setAutoCommit(false);
if (sql != null) {
for (int i = 0; i < sql.length; i++) {
if (params[i] != null) {
pstat = conn.prepareStatement(sql[i]);
for (int j = 0; j < params[i].length; j++) {
pstat.setObject(j + 1, params[i][j]);
}
pstat.executeUpdate();
}
}
conn.commit();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
try {
// if the commit errors
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
throw new RuntimeException(e.getMessage());
} finally {
SQLHelper.close(null, pstat, conn);
}
}
/**
* This method use to call the Procedure
*
* @param sql
* Like {call procedure_name(?,?,?)}
* @param params
*
* @return no return.
*/
public static void callProcedure(String sql, String[] params) {
conn = SQLHelper.getConnection();
try {
cstat = conn.prepareCall(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
cstat.setObject(i + 1, params[i]);
}
}
cstat.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException(e.getMessage());
} finally {
SQLHelper.close(null, cstat, conn);
}
}
/**
*
* @param sql
* @param inParams
* @param outParams
* @return return a CallableStatement type,but the Procedure have a return,
* and with a cursor.
*/
public static CallableStatement callProcedure(String sql,
String[] inParams, Integer[] outParams) {
conn = SQLHelper.getConnection();
try {
cstat = conn.prepareCall(sql);
if (inParams != null) {
for (int i = 0; i < inParams.length; i++) {
cstat.setObject(i + 1, inParams[i]);
}
}
// register the output values
if (outParams != null) {
for (int i = 0; i < outParams.length; i++) {
cstat.registerOutParameter(inParams.length + 1 + i,
outParams[i]);
}
}
cstat.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException(e.getMessage());
} finally {
// the ResultSet must be return, not close here.
}
return cstat;
}
/**
*
* @param sql
* ...
* @param params
* ...
* @return A ResultSet.
*/
public static ResultSet executeQuery(String sql, String[] params) {
conn = SQLHelper.getConnection();
try {
pstat = conn.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
pstat.setObject(i + 1, params[i]);
}
}
rs = pstat.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException(e.getMessage());
} finally {
//SQLHelper.close(rs, pstat, conn);
}
return rs;
}
public static Connection getConn() {
return conn;
}
public static PreparedStatement getPstat() {
return pstat;
}
public static ResultSet getRs() {
return rs;
}
public static CallableStatement getCstat() {
return cstat;
}
}
如果你仔细的看了这段SQLHelper代码的封装，那么你肯定发现了下面的代码：
/**
* The driver and the Database configuration informations just to load one
* time.
*/
static {
try {
// get the Oracle connection information from the db_info.properties
// file
prop = new Properties();
fis = new FileInputStream("db_info.properties");
prop.load(fis);
driver = prop.getProperty("driver");
url = prop.getProperty("url");
user = prop.getProperty("user");
password = prop.getProperty("password");
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
// The errors happening there will seriously,so we make the program
// exit directly.
System.exit(-1);
} finally {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
fis = null; // Benefit to gc.
}
}因为数据库的驱动只需要加载一次，那么可以把它写到一个静态代码块中。当然你也会看到在这个块中使用了Properties类来加载外部db_info.properties文件，这样做的好处是，当我们需要更换数据库或者修改一些参数的时候，直接去修改db_info .properties文件的值就可以了，这样会更加简单，高效同时保证程序的封装完整性，如果是一个jar包的话，特别是对于一个大型的软件系统的代码块，这样做其好处是不言而喻的。
关于代码中封装的方法的使用，我在这里就不详细进行说明，如果你能看懂代码，自然就可以轻易的使用它。当然，这里我会对executeUpdate(String sql, String[] params)方法和callProcedure(String sql, String[] params)方法作一个使用的说明。
一、executeUpdate(String sql, String[] params)方法
public static void executeUpdate(String sql, String[] params) {
conn = SQLHelper.getConnection();
try {
pstat = conn.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
pstat.setString(i + 1, params[i]);
}
}
pstat.executeUpdate();
} catch (SQLException e) {
// if the DML sentence not succeed
throw new RuntimeException(e.getMessage());
} finally {
close(null, pstat, conn);
}
}操作表结构如下：
executeUpdate(String sql, String[] params)方法使用示例代码：
public static void main(String[] args) {
String sql = "update scott.emp set sal=?, comm=? where empno=?";
String[] params = {"5000","1000","7369"};
SQLHelper.executeUpdate(sql, params);
}二、callProcedure(String sql, String[] params)方法
public static void callProcedure(String sql, String[] params) {
conn = SQLHelper.getConnection();
try {
cstat = conn.prepareCall(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
cstat.setObject(i + 1, params[i]);
}
}
cstat.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException(e.getMessage());
} finally {
SQLHelper.close(null, cstat, conn);
}
}①首先在数据空中创建一个过程pro_insert_test
create or replace procedure pro_insert_test(v_in_empno number, v_in_ename varchar2, v_in_sal number)
is
--defined variable
begin
--execute statement
insert into scott.emp(empno, ename, sal) values(v_in_empno, v_in_ename, v_in_sal);
end;
②callProcedure(String sql, String[] params)方法的调用
public static void main(String[] args){
String sql = "{call pro_insert_test(?,?,?)}";
String[] params = {"8859", "Jasber", "8000"};
//在SQLHelper类对应方法下抛出的都是RuntimeException,所以可以选择捕获或者不捕获
SQLHelper.callProcedure(sql, params);
}
#this is Oracle Properties
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
user=scott
password=Jasber0619如果你觉得你想要多个连接的和考虑多线的SQLHelper，那么，你可以去refactor这段代码。在这就不具体讨论了。以上就是此篇博客的内容了，考虑到篇幅的原因就不再赘述，如有问题请留言。
2257
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
