JDBC工具類

1、連接數據庫

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.Properties;

/**

連接數據庫 

@author liyulin lyl010991@126.com 
@version 1.0 2015-01-14 
*/

public class DBConnection {
private Connection con = null;

 private String user = "root";

 private String password = "lyl123";

 private String serverIp = "localhost";

 private String database = "test";
public DBConnection() {
}
public DBConnection(String database, String serverIp) {

 this.database = database;  
 this.serverIp = serverIp;  

}
/**

加載驅動 建立數據庫連接 

@throws ClassNotFoundException 
@throws InstantiationException 
@throws IllegalAccessException 
@throws SQLException 
*/

public void connect() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {

 Properties pr = new Properties();

 pr.put("characterEncoding", "UTF-8");

 pr.put("useUnicode", "TRUE");

 pr.put("user", this.user);

 pr.put("password", this.password);

 Class.forName("com.mysql.jdbc.Driver").newInstance();

 con = DriverManager.getConnection("jdbc:mysql://" + this.serverIp + "/" + this.database, pr);

}
/**

關閉連接 
*/

public void disconnect() {

 try {

 if (con != null) {  
     con.close();  
 }  

} catch (SQLException ex) {

 ex.printStackTrace();  

}

}
/**

獲取Connection對象 
*/

public Connection getCon() {

 return con;

}

}  </pre> 

 2、操作數據庫 
import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import java.util.logging.Level;

import java.util.logging.Logger;  



/**

JDBC工具類 

@author liyulin lyl010991@126.com 
@version 1.0 2015-01-14 
*/

public class JDBC {
private DBConnection db = null;

 private Connection conn = null;

 private PreparedStatement ps = null;

 private ResultSet rs = null;
/**

建立數據庫連接 
*/

public Connection connectDB() {

 db = new DBConnection();

 try {

 db.connect();  
 conn = db.getCon();  

} catch (Exception ex) {

 Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);  

}

 return conn;

}
/**

關閉數據庫 
*/

public void closeDB() {

 try {

 if (rs != null) {  
     rs.close();  
 }  
 if (ps != null) {  
     ps.close();  
 }  
 if (conn != null) {  
     conn.close();  
 }  

} catch (SQLException ex) {

 Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);  

}

}
/**

執行一條sql語句（增、刪、改） 

@param sql 插入sql語句 
@param params sql語句中?所對應的值 
@return 是否插入成功 
*/

public boolean executeSQL(String sql, Object[] params) {

 boolean tag = false;// 操作是否成功標志

 connectDB();

 try {

 ps = conn.prepareStatement(sql);  
 if (null != params) {  
     for (int i = 0, paramsSize = params.length; i < paramsSize; i++) {  
         ps.setObject(i + 1, params[i]);  
     }  
 }  

 ps.executeUpdate();  
 tag = true;  

} catch (Exception e) {

 e.printStackTrace();  

} finally {

 closeDB();  
 return tag;  

}

}
/**

批量操作（增、刪、改） 

@param sqls 插入sql語句 
@param objs sql參數（一個二維數組） 
@return 
*/

public boolean executeBatch(List<String> sqls, Object[][] objs) {

 boolean tag = false;// 批量操作是否成功標志

 connectDB();

 try {

 conn.setAutoCommit(false);  
 if (null != objs) {  
     // sql參數為null  
     for (int i = 0, size = sqls.size(); i < size; i++) {  
         String sql = sqls.get(i);  
         ps = conn.prepareStatement(sql);  
         if (null != objs[i]) {  
             for (int j = 0, paramsSize = objs[i].length; j < paramsSize; j++) {  
                 ps.setObject(j + 1, objs[i][j]);  
             }  
         }  
         ps.executeUpdate();  
     }  
 } else {  
     for (int i = 0, size = sqls.size(); i < size; i++) {  
         String sql = sqls.get(i);  
         ps = conn.prepareStatement(sql);  
         ps.executeUpdate();  
     }  
 }  
 conn.commit();  
 tag = true;  

} catch (Exception e) {

 try {  
     conn.rollback();  
 } catch (SQLException ex) {  
     Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);  
 }  
 e.printStackTrace();  

} finally {

 closeDB();  
 return tag;  

}

}
/**

執行一條插入語句，同時返回插入時的pk 

@param sql 
@param params 
@return pk 
*/

public int insertAndGetPk(String sql, Object[] params) {

 int key = 0;

 connectDB();

 try {

 conn.setAutoCommit(false);  
 ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);  
 if (null != params) {  
     for (int i = 0, paramsSize = params.length; i < paramsSize; i++) {  
         ps.setObject(i + 1, params[i]);  
     }  
 }  
 ps.executeUpdate();  
 ResultSet keys = ps.getGeneratedKeys();  
 if (keys.next()) {  
     key = keys.getInt(1);  
 }  
 conn.commit();  

} catch (Exception exception) {

 try {  
     conn.rollback();  
     exception.printStackTrace();  
     return 0;  
 } catch (SQLException ex) {  
     Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);  
 }  

} finally {

 closeDB();  

}

 return key;

}
/**

查詢 

@param sql sql語句（參數用“?”） 
@param params 參數值 
@return 
*/

public ResultSet query(String sql, Object[] params) {

 try {

 ps = conn.prepareStatement(sql);  

 if (null != params) {  
     for (int i = 0, paramsSize = params.length; i < paramsSize; i++) {  
         ps.setObject(i + 1, params[i]);  
     }  
 }  
 rs = ps.executeQuery();  

} catch (Exception ex) {

 ex.printStackTrace();  

}

 return rs;

}
public static void main(String[] agrs) {

 List<String> sqls = new ArrayList<String>();

 sqls.add("insert into image(url,title) values(?,?)");

 sqls.add("insert into image(url,title) values('2','222')");

 sqls.add("insert into image(url,title) values(?,?)");

 sqls.add("insert into image(url,title) values(?,?)");

 Object[][] objs = new Object[][]{

 {"1", "111"}, null, {"3", "333"}, {"4", "444"}  

};
JDBC db = new JDBC();

 boolean tag = db.executeBatch(sqls, objs);

 System.out.println("tag===>" + tag);

}

}  </pre>