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; }
