下面我整理了一下java中常用的几个与数据库交互的常用方法，仅供参考：

1.执行SQL（dao层的实现类中）

（1）SQL查询：

//import org.hibernate.Query; //import org.hibernate.Session; /** * 通过名称查找id * @param psname * @return id */@Overridepublic String findEnterpriseId(String psname) {    String id = "";    //查找信息的sql    String sql = "select id from t_enterprise where psname = '"+psname+"'"; //创建Query对象接收通过createSqlQuery()方法解析sql语句得到的结果 //方式一：    Query query = this.createSqlQuery(sql); //方式二： //Session session = getSession();    //Query query = session.createSQLQuery(sql); 　　//存储过程键值对应 　　//sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);        List
     
       list = query.list();    for (int i = 0; i < list.size(); i++) {        Object obj = list.get(0);        if (obj!=null) {            id = obj.toString();        }    }    return id;}
     

 （2）SQL修改或删除

@Override    public void updateWeather(ActuallyWeather actuallyWeather) throws Exception {        String sql = "update t_actually_weather set forecast_time = '"+actuallyWeather.getForecastTime()+"',"                + "max_temperature = '"+actuallyWeather.getMaxTemperature()+"',"                + "min_temperature = '"+actuallyWeather.getMinTemperature()+"',"                + "place_name = '"+actuallyWeather.getPlaceName()+"',"                + "pub_time = '"+actuallyWeather.getPubTime()+"',"                + "weather_status = '"+actuallyWeather.getWeatherStatus()+"',"                + "wind_power = '"+actuallyWeather.getWindPower()+"'"                + " where id = '"+actuallyWeather.getId()+"'";        this.getSession().clear();        this.createSqlQuery(sql).executeUpdate();    }

2.执行HQL（dao层的实现类中）

（1）返回Page

1）//action中page属性private Page
     
       page = new Page
      
       (Constants.DEFAULT_PAGE_SIZE, true);2）page参数在（action）中只需要设置如下：    page.setPageNo(this.getPageNo());    page.setPageSize(this.getPageSize());3）/**     * 查询     * @param page     * @param filterMap     */    @SuppressWarnings("rawtypes")    @Override    public Page
       
         findAllEnterprise(Page
        
          page,Map filterMap){        String hql = " from UnifiedEnterInfo s where 1=1 ";        //污染源名称            String psname = (String) filterMap.get("psname");        if (StringUtils.isNotEmpty(psname)) {            String[] str = psname.split(" ");            String reg = "";            for (int i = 0; i < str.length; i++) {                reg = str[i];                if (!"".equals(reg)) {                    hql = hql+" and psname like '%"+reg+"%'";                }            }            //hql = hql+" and psname like '%"+psname.trim()+"%'";        }                //系统来源        String systemSource = (String) filterMap.get("systemSource");        if (StringUtils.isNotEmpty(systemSource)) {            hql = hql+" and systemSource = "+systemSource;        }        //所属区域        String regionCode = (String) filterMap.get("regionCode");        if (StringUtils.isNotEmpty(regionCode)) {            if(!"110100".equals(regionCode))                hql = hql+" and regionCode like '"+regionCode+"%'";        }        //法人编码        String corporationCode = (String) filterMap.get("corporationCode");        if (StringUtils.isNotEmpty(corporationCode)) {            hql = hql+" and corporationCode like '%"+corporationCode.trim()+"%'";        }                //法人名称        String corporationName = (String) filterMap.get("corporationName");        if (StringUtils.isNotEmpty(corporationName)) {            hql = hql+" and corporationName like '%"+corporationName.trim()+"%'";        }                //地址        String addr = (String) filterMap.get("addr");        if (StringUtils.isNotEmpty(addr)) {            hql = hql+" and addr like '%"+addr.trim()+"%'";        }                //是否统一        String ifUinfied =(String)filterMap.get("ifUinfied");        if("1".equals(ifUinfied)) {            hql = hql+" and mainOrChild=0";        }else if("2".equals(ifUinfied)){            hql = hql+" and mainOrChild!=0";        }                hql = hql+" order by ltrim(rtrim(psname)) asc";                return this.find(page,hql);    }
        
       
      
     

（2）返回唯一值：

/**     * 查询获取最大的统一污染源编码     */    @Override    public String findMaxUniqueCode(){        String hql = "select max(uniqueCode) from UnifiedEnterInfo ";        return (String)this.findUnique(hql);    }

（3）返回List：

@Override    public List
     
       getUnifiedEnterInfosList(Map filterMap) {        String hql = " from UnifiedEnterInfo s where 1=1 ";        String psname = (String) filterMap.get("psname");        if (StringUtils.isNotEmpty(psname)) {            hql = hql+" and psname like '%"+psname.trim()+"%'";        }        String corporationCode = (String) filterMap.get("corporationCode");        if (StringUtils.isNotEmpty(corporationCode)) {            hql = hql+" and corporationCode like '%"+corporationCode.trim()+"%'";        }                String corporationName = (String) filterMap.get("corporationName");        if (StringUtils.isNotEmpty(corporationName)) {            hql = hql+" and corporationName like '%"+corporationName.trim()+"%'";        }                String addr = (String) filterMap.get("addr");        if (StringUtils.isNotEmpty(addr)) {            hql = hql+" and addr like '%"+addr.trim()+"%'";        }                hql = hql+" order by psname asc";                return this.find(hql);    }
     

3.执行存储过程（dao层的实现类中）

注意：

如果查询执行的时候数据库返回”该语句没有返回结果集。“这样的错误，存储过程中少了一句代码：SET NOCOUNT ON

 

（1）查询：

public List findPsList(String psCode) {        Long psCode1;        //创建session对象        Session session = this.getSession();        //创建事务的对象        Transaction trans = session.beginTransaction();        //调用存储过程        SQLQuery sqlQuery = session.createSQLQuery("{Call Proc_ZL_PSFlowRecharge(?)}");        if ("".equals(psCode)||psCode==null) {            psCode1 = (long) -1;        }else{            psCode1 = Long.parseLong(psCode);        }        //为存储过程设置输入参数        sqlQuery.setLong(0,psCode1 == null ? 0 : psCode1); 　　　　 //存储过程键值对应 　　　　 //sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);        //提交事务        trans.commit();        //获取存储过程的运行结果（得到的结果是Object类型的数组集合）存入list集合        List list = sqlQuery.list();                return list;    }

（2）修改：

public String savePSGross(Map
      
        map) {        Date date = null;        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");        Long psCode1;        //企业编码        String psCode =(String) map.get("psCode");        //污染因子编码        String monitorItemCode =(String) map.get("monitorItemCode");        //充值时间        String time = (String) map.get("time");        //充值量        String acpNumber =(String) map.get("acpNumber");        //充值类型        String rechargeType =(String) map.get("rechargeType");        //创建session对象        Session session = this.getSession();        //创建事务的对象        Transaction trans = session.beginTransaction();        //调用存储过程        SQLQuery query = session.createSQLQuery("{Call Proc_ZL_SavePSGrossInfo(?,?,?,?,?)}");        if ("".equals(psCode)||psCode==null) {            psCode1 = (long) -1;        }else{            psCode1 = Long.parseLong(psCode);        }        if (StringUtils.isNotEmpty(time)) {            try {                date = sf.parse(time);            } catch (ParseException e) {                e.printStackTrace();            }        }        //为存储过程设置输入参数        query.setLong(0,psCode1 == null ? 0 : psCode1);        query.setString(1,monitorItemCode == null ? "" : monitorItemCode);        query.setString(2,time == null ? "" : time);        query.setBigDecimal(3,acpNumber == null ? new BigDecimal("0") : new BigDecimal(acpNumber));        query.setString(4,rechargeType == null ? "" : rechargeType);        query.executeUpdate();            return "success";    }
      

 （3）用JDBC方式连接数据库执行存储过程：

所需的jar包：

工具类：

package com.jointsky.jointframe.ui.project.util;import java.io.BufferedInputStream;import java.io.FileInputStream;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.util.Properties;import com.jointsky.jointframe.system.config.service.JointFrameConfigManager;/** *  * 
Description:JDBC连接工具类
 *  * @author liuf * @date 2017-6-26 * @version 1.0 */public class JdbcUtil {    public static Connection getConn() {
             String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";        String dbURL = "jdbc:sqlserver://localhost:1433;SelectMethod=cursor;DatabaseName=数据库名";        String userName = "sa";        String userPwd = "123.com";        Connection dbConn = null;        try {            Class.forName(driverName);            dbConn = DriverManager.getConnection(dbURL, userName, userPwd);            System.out.println("连接数据库成功");        } catch (Exception e) {            e.printStackTrace();            System.out.print("连接失败");        }        return dbConn;    }}

调用方式：

@Override    public List
      
        getAllMonitorDatas(Map
       
         filterMap)            throws Exception {                List
        
          list = new ArrayList
         
          ();        try {            Connection dbConn = JdbcUtil.getConn();            CallableStatement statement = dbConn.prepareCall("SET NOCOUNT ON exec dbo.ProcGetMonitorDatas ?,?,?,?,?,?,?,?");            //开始时间            Date beginTime = (Date) filterMap.get("beginTime");            //结束时间            Date endTime = (Date) filterMap.get("endTime");            //编码            String monitorPointCode = (String) filterMap.get("monitorPointCode");            //编码            String pollutantCode = (String)filterMap.get("pollutantCode");            //编码            String psCode = (String)filterMap.get("psCode");            //类型            Integer outputType = (Integer)filterMap.get("outputType");            //类型            Integer alarmType = (Integer) filterMap.get("alarmType");            //类型细分            Integer alarmTypeDetails = (Integer) filterMap.get("alarmTypeDetails");            if (endTime == null) {                endTime = new Date();            }            //为存储过程设置输入参数            statement.setDate(1,new java.sql.Date(beginTime == null ? null : beginTime.getTime()));            statement.setDate(2,new java.sql.Date(endTime == null ? null : endTime.getTime()));            statement.setString(3,(String) (monitorPointCode == null ? "" : monitorPointCode));            statement.setString(4,(String) (pollutantCode == null ? "" : pollutantCode));            statement.setString(5,(String) (psCode == null ? "" : psCode));            statement.setInt(6,outputType == null ? -1 : outputType);            statement.setInt(7,alarmType == null ? -1 : alarmType);            statement.setInt(8,alarmTypeDetails == null ? -1 : alarmTypeDetails);            ResultSet rs = statement.executeQuery();            while (rs.next()) {                MonitorData c = new MonitorData();                //String id = rs.getString("id");                //String monitorPointName = rs.getString("jkkljj");                                c.setPsName(rs.getString("psName"));                c.setMonitorPointName(rs.getString("monitorPointName"));                c.setPollutantName(rs.getString("pollutantName"));                c.setMonitorTime(rs.getDate("monitorTime"));                c.setMonitorTimeCn(StringUtils.isEmpty(rs.getString("monitorTime")) ? "" : rs.getString("monitorTime").substring(0, 13) + "时");                c.setMonitorValueType(rs.getString("monitorValueType"));                c.setMonitorValue(rs.getString("monitorValue"));                c.setOutputType(Integer.parseInt(rs.getString("outputType")));                                list.add(c);            }            statement.close();        } catch (Exception e1) {            e1.printStackTrace();        }        return list;    }
         
        
       
      

 4.用Criteria执行查询：

public Page
      
        find(Page
       
         page,            Map
        
          filterMap) {        Criteria criteria = this.createCriteria();        try {            if (filterMap.size() > 0) {                String name = filterMap.get("fullName");                if (StringUtils.isNotEmpty(name)) {                    criteria.add(Restrictions.like("fullName", name,                            MatchMode.ANYWHERE));                }                String unit = filterMap.get("unit");                if (StringUtils.isNotEmpty(unit)) {                    criteria.add(Restrictions.like("unit", unit,                            MatchMode.ANYWHERE));                }                criteria.addOrder(Order.asc("fullName"));                                    }            Page
         
           pages = this.findByCriteria(page, criteria);            return pages;        } catch (Exception e) {            e.printStackTrace();        }        return null;    }