java与数据库交互常用到的一些方法

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

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

（1）SQL查询：

//import org.hibernate.Query;
//import org.hibernate.Session;
/**
 * 通过名称查找id
 * @param psname
 * @return id
 */
@Override
public 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<String[]> 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<UnifiedEnterInfo> page = new Page<UnifiedEnterInfo>(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<UnifiedEnterInfo> findAllEnterprise(Page<UnifiedEnterInfo> 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<UnifiedEnterInfo> 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<String, Object> 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包：sqljdbc4.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;

/**
 * 
 * <p>Description:JDBC连接工具类</p>
 * 
 * @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<MonitorData> getAllMonitorDatas(Map<String, Object> filterMap)
            throws Exception {
        
        List<MonitorData> list = new ArrayList<MonitorData>();
        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<AddressBook> find(Page<AddressBook> page,
            Map<String, String> 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<AddressBook> pages = this.findByCriteria(page, criteria);
            return pages;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }