javaee sqlhelper公共类的定义与使用

首先甩上sqlhelperl类

package ContactBook.DB;import java.util.*;import java.io.InputStream;import java.sql.*;public class newsqlhelper {    private  String DRIVER = "";//指定数据库驱动      private  String URL = "";//要连接的数据库      private  String USERNAME="";//用户名    private  String PWD="";//密码    /*log4jhelper log=new log4jhelper(newsqlhelper.class);*/    private void init(){        try {            InputStream objsm=getClass().getResourceAsStream("sql.properties");            Properties objpro=new Properties();            objpro.load(objsm);            DRIVER=objpro.getProperty("DRIVER");            URL=objpro.getProperty("URL");            USERNAME=objpro.getProperty("USERNAME");            PWD=objpro.getProperty("PWD");        } catch (Exception e) {            /*log.WriteError(e.getMessage());*/            e.printStackTrace();        }    }    public newsqlhelper() {        init();    }    /**      * 获取连接      * @return        */      public Connection getConnection() {          try {              Class.forName(DRIVER);              Connection conn = DriverManager.getConnection(URL,USERNAME,PWD);              return conn;          } catch (SQLException e) {          /*  log.WriteError(e.getMessage()); */             e.printStackTrace();        } catch (ClassNotFoundException e) {              /*log.WriteError(e.getMessage()); */             e.printStackTrace();        }          return null;      }      /**      * 关闭连接      * @param stat      * @param conn      */      public void close(Statement stat,Connection conn) {          try {              if(stat != null) {                  stat.close();              }          } catch (SQLException e) {              /*log.WriteError(e.getMessage());*/          } finally {              try {                  if(conn != null) {                      conn.close();                  }              } catch (SQLException e) {                  /*log.WriteError(e.getMessage()); */                 e.printStackTrace();            }          }      }      /**      * 执行增改删       * @param sql  sql语句      * @param pam  sql参数数组，参数顺序要和sql语句对应好       * @return 返回受影响行数，否则返回0     */      public int executeUpdate(String sql,Object[] pam){          Connection conn = null;          PreparedStatement state = null;         int num=0;           try{              conn=this.getConnection();            state=conn.prepareStatement(sql);            if(pam!=null && pam.length!=0){                int i=1;                for(Object obj:pam){                    state.setObject(i, obj);                    i++;                }            }               num = state.executeUpdate();              state.close();                }catch(Exception e){              /*log.WriteError(e.getMessage());*/            e.printStackTrace();        }finally{              this.close(state, conn);         }          return num;    }     /**     * 按条件查询     * @param sql     * @return     */    public ArrayList<Object[]> executeQuery(String sql){        Connection conn = null;          PreparedStatement stat = null;          ResultSet rs = null;         ArrayList<Object[]> al=new ArrayList<Object[]>();        try {            conn = this.getConnection();            stat = conn.prepareStatement(sql);            rs=stat.executeQuery();            ResultSetMetaData rsmd=rs.getMetaData();              int column=rsmd.getColumnCount();              while(rs.next()){                  Object[] ob=new Object[column];                  for(int i=0;i<ob.length;i++){                      ob[i]=rs.getObject(i+1);                  }                  al.add(ob);              }           } catch (SQLException e) {            e.toString();        }finally {              this.close(stat, conn);          }         return al;    }    /**      * 执行查询操作      * @param sql  sql语句，如select * from test where id=?      * @param pam  sql参数数组，参数顺序要和sql语句对应好       * @return 返回结果集，否则返回null     */      public ArrayList<Object[]> executeQuery(String sql,Object[] pam){        Connection conn = null;          PreparedStatement stat = null;          ResultSet rs = null;         ArrayList<Object[]> al=new ArrayList<Object[]>();        try {            conn = this.getConnection();            stat = conn.prepareStatement(sql);            if(pam!=null && pam.length!=0){                int i=1;                for(Object obj:pam){                    stat.setObject(i, obj);                    i++;                }            }            rs=stat.executeQuery();            ResultSetMetaData rsmd=rs.getMetaData();              int column=rsmd.getColumnCount();              while(rs.next()){                  Object[] ob=new Object[column];                  for(int i=0;i<ob.length;i++){                      ob[i]=rs.getObject(i+1);                  }                  al.add(ob);              }           } catch (SQLException e) {            /*log.WriteError(e.getMessage());*/            e.printStackTrace();        }finally {              this.close(stat, conn);          }         return al;    }    public ResultSet ExecuteSqlByData(String Sql){        ResultSet objRS = null;         try        {            Statement objSt = getConnection().createStatement();            objRS = objSt.executeQuery(Sql);        }        catch(SQLException ex){            /*Logs objLogs = new Logs();            objLogs.WriteDebug(ex.getMessage() + "，错误的T-SQL语句是："  + Sql);*/        }        catch(Exception ex){            /*Logs objLogs = new Logs();            objLogs.WriteDebug(ex.getMessage());*/        }        return objRS;    }}

然后给出数据库连接配置文件
sql.properties

DRIVER=com.microsoft.sqlserver.jdbc.SQLServerDriverURL=jdbc:sqlserver://localhost:1433; DatabaseName=ContactBookUSERNAME=newPWD=123456

UserBean.java

package ContactBook.DB;

public class UserBean {
private int userID;
private String userName;
private String userPwd;
public int getUserID() {
return userID;
}
public void setUserID(int userID) {
this.userID = userID;
}
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;
}

public UserBean(){}
public UserBean(int userID,String userName,String userPwd){
this.userID=userID;
this.userName=userName;
this.userPwd=userPwd;
}

}

UserDao.javapackage ContactBook.DB;import java.util.ArrayList;import java.util.List;import ContactBook.DB.UserBean;import ContactBook.DB.newsqlhelper;public class UserDao {newsqlhelper sqlhelper=new newsqlhelper();/** * 新增用户信息 * @param user * @return */public int Add(UserBean user){    String sql="insert into UserInfo(UserName, UserPwd) values(?,?)";    Object[] pam={user.getUserName(),user.getUserPwd()};    return sqlhelper.executeUpdate(sql, pam);  }/** * 根据登录名查找用户,用于登录吧 * @param UserName * @return */public UserBean SelectUser(String UserName){    UserBean user = new UserBean();    String sql = "select * from UserInfo where UserName='"+UserName+"'";    ArrayList<Object[]> listobj= sqlhelper.executeQuery(sql);    if(listobj!=null){        for(Object[] on:listobj){            user.setUserID((int)on[0]);            user.setUserName(on[1].toString());            user.setUserPwd(on[2].toString());        }        return user;    }    else{        return null;    }   }/** * 根据登录名查 * @param UserName * @return */public int SelectUsername(String UserName){    UserBean user = new UserBean();    String sql = "select * from UserInfo where UserName='"+UserName+"'";    ArrayList<Object[]> listobj= sqlhelper.executeQuery(sql);    if(listobj!=null){        for(Object[] on:listobj){            user.setUserID((int)on[0]);            user.setUserName(on[1].toString());            user.setUserPwd(on[2].toString());        }        return 1;    }    else{        return 0;    }   }/** * 按照用户id查询 * @param UserName * @return */public UserBean SelectID(String UserID){    UserBean user = new UserBean();    String sql = "select * from UserInfo where UserName='"+UserID+"'";    ArrayList<Object[]> listobj= sqlhelper.executeQuery(sql);    if(listobj!=null){        for(Object[] on:listobj){            user.setUserName(on[1].toString());            user.setUserPwd(on[2].toString());        }        return user;    }    else{        return null;    }   }public List<UserBean> chaListAll(){    String sql="select * from UserInfo";    ArrayList<Object[]> listobj= sqlhelper.executeQuery(sql, null);    List<UserBean> list=new ArrayList<>();    if(listobj!=null){        for(Object[] on:listobj){            UserBean user=new UserBean();            user.setUserID((int)on[0]);            user.setUserName(on[1].toString());            user.setUserPwd(on[2].toString());            list.add(user);        }        return list;    }    else{        return null;    }   }public int del(String id){    String sql="delete from UserInfo where UserID=?";    Object[] pam={id};    return sqlhelper.executeUpdate(sql, pam);}public int update(UserBean user){    String sql="update UserInfo set UserName=?,UserPwd=?  where UserID=?";    Object[] pam={user.getUserID(),user.getUserName(),user.getUserPwd()};    return sqlhelper.executeUpdate(sql, pam);}}