SQL_Helper

package bookManage;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.sql.*;

public final class SqlHelper {
private static Connection connect=null;

//新建一个父类 设置数据库字符串链接
private static class ConnString{
private String driverName;
private String dbURL;
private String userName;
private String userPwd;

public ConnString(String driverName, String dbURL, String userName, String userPwd) {
this.driverName = driverName;
this.dbURL = dbURL;
this.userName = userName;
this.userPwd = userPwd;
}
public String getDriverName() {
return driverName;
}
public void setDriverName(String driverName) {
this.driverName = driverName;
}
public String getDbURL() {
return dbURL;
}
public void setDbURL(String dbURL) {
this.dbURL = dbURL;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserPwd() {
return userPwd;
}
public void setUserPwd(String userPwd) {
this.userPwd = userPwd;
}
}
//子类 sqlserver
private static class sqlConnString extends ConnString{
public sqlConnString(String driverName, String dbURL, String userName, String userPwd) {
super(driverName, dbURL, userName, userPwd);
}
}
//子类 mysql
private static class mysqlConnString extends ConnString{
public mysqlConnString(String driverName, String dbURL, String userName, String userPwd) {
super(driverName, dbURL, userName, userPwd);
}
}
//子类 oracle
private static class oracleConnString extends ConnString{
public oracleConnString(String driverName, String dbURL, String userName, String userPwd) {
super(driverName, dbURL, userName, userPwd);
}
}
/**
* 获取链接
* @return
*/
public static Connection getConnect(){
try{
if(connect==null){
//sqlserver链接
sqlConnString scs=new sqlConnString("com.microsoft.sqlserver.jdbc.SQLServerDriver", "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=Train", "sa", "Cui199111");
//mysql链接
mysqlConnString mcs=new mysqlConnString("com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/train", "root", "cuiyachao");
//oracle链接
oracleConnString ocs=new oracleConnString("oracle.jdbc.driver", "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");

Class.forName(scs.getDriverName());
connect=DriverManager.getConnection(scs.getDbURL(),scs.getUserName(),scs.getUserPwd());
}
return connect;
}catch(Exception e){
e.printStackTrace();
return null;
}
}
/**
* 执行语句（eg:insert update delete)
* @param sql
* @param params OracleParameter[]
* @return
* @throws Exception int(sql 影响的行数)
*/
public static int ExecuteNonQuery(String sql)throws Exception{
PreparedStatement ps=null;
Connection conn=null;
try{
conn=getConnect();
ps=conn.prepareStatement(sql);
return ps.executeUpdate();
}catch(Exception e){
throw new Exception("executeNonQuery方法出错："+e.getMessage());
}finally{
try{
if(ps!=null&&(!ps.isClosed()))
ps.close();
if(conn!=null&&(!conn.isClosed()))
conn.close();
}catch(Exception e){
throw new Exception("executeNonQuery方法出错："+e.getMessage());
}
}
}

/**
* 获取结果集语句
* @param sql
* @param params
* @return
* @throws Exception
*/

public static ArrayList ExecuteReader(String sql)throws Exception{
PreparedStatement ps=null;
Connection conn=null;
try {
conn=getConnect();
ps=conn.prepareStatement(sql);
//PrepareCommand(ps,params);
ResultSet rs=ps.executeQuery();
ArrayList al=new ArrayList();
//
ResultSetMetaData rsmd=rs.getMetaData();
int column=rsmd.getColumnCount();

while(rs.next()){
Object[] obj=new Object[column];
for(int i=1;i<=column;i++){
System.out.println(rs.getObject(i));
obj[i-1]=rs.getObject(i);
}
al.add(obj);
}
// 关闭链接
rs.close();
ps.close();
conn.close();
return al;
} catch (Exception e) {
// TODO: handle exception
throw new Exception("ExcuteReader方法出错："+e.getMessage());
} finally{
try {
if(ps!=null&&(!ps.isClosed()))
ps.close();
if(conn!=null&&(!conn.isClosed()))
conn.close();
} catch (Exception e2) {
// TODO: handle exception
throw new Exception("ExcuteReader方法出错："+e2.getMessage());
}
}
}
/**
* 获取单个字段的值的语句（用名字指定字段）
* @param sql
* @param name
* @return
* @throws Exception
*/
public static Object ExecuteScalar(String sql,String name)throws Exception{
PreparedStatement ps=null;
Connection conn=null;
ResultSet rs=null;
try {
conn=getConnect();
ps=conn.prepareStatement(sql);

rs=ps.executeQuery();
if(rs.next()){
System.out.println(rs.getObject(name));
return rs.getObject(name);
}else{
return null;
}
} catch (Exception e) {
// TODO: handle exception
throw new Exception("ExecuteScalar方法出错："+e.getMessage());
} finally{
try {
if(rs!=null&&(!rs.isClosed()))
rs.close();
if(ps!=null&&(!ps.isClosed()))
ps.close();
if(conn!=null&&(!conn.isClosed()))
conn.close();
} catch (Exception e2) {
throw new Exception("ExecuteScalar方法出错："+e2.getMessage());
}
}
}
/**
* 获取单个字段的值的语句（用下标指定）
* @param sql
* @param name
* @return
* @throws Exception
*/
public static Object executeScalar(String sql,int index)
throws Exception{
PreparedStatement ps=null;
Connection conn=null;
ResultSet rs=null;

try {
conn=getConnect();
ps=conn.prepareStatement(sql);
rs=ps.executeQuery();
if(rs.next()){
System.out.println(rs.getObject(index));
return rs.getObject(index);
}else{
return null;
}
} catch (Exception e) {
// TODO: handle exception
throw new Exception("ExecuteScalar方法出错："+e.getMessage());
} finally{
try {
if(rs!=null&&(!rs.isClosed()))
rs.close();
if(ps!=null&&(!ps.isClosed()))
ps.close();
if(conn!=null&&(!conn.isClosed()))
conn.close();
} catch (Exception e2) {
throw new Exception("ExecuteScalar方法出错："+e2.getMessage());
}
}
}
//关闭链接 释放链接的
public static void releaseConn(PreparedStatement Psmt,Connection conn) {
if(Psmt!=null) {
try {
Psmt.close();
}catch(Exception ex) {
ex.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
}catch(Exception ex) {
ex.printStackTrace();
}
}
}
}