下面我整理了一下java中常用的几个与数据库交互的常用方法，仅供参考：
1.执行SQL(dao层的实现类中)
(1)SQL查询：
//import org.hibernate.Query;
//import org.hibernate.Session;
/*** 通过名称查找id
*@parampsname
*@returnid*/@OverridepublicString 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();
}
}returnid;
}
(2)SQL修改或删除
@Overridepublic void updateWeather(ActuallyWeather actuallyWeather) throwsException {
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)/*** 查询
*@parampage
*@paramfilterMap*/@SuppressWarnings("rawtypes")
@Overridepublic Page findAllEnterprise(Pagepage,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)返回唯一值：
/*** 查询获取最大的统一污染源编码*/@OverridepublicString findMaxUniqueCode(){
String hql= "select max(uniqueCode) from UnifiedEnterInfo ";return (String)this.findUnique(hql);
}
(3)返回List：
@Overridepublic ListgetUnifiedEnterInfosList(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)查询：
publicList 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();returnlist;
}
(2)修改：
public String savePSGross(Mapmap) {
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") : newBigDecimal(acpNumber));
query.setString(4,rechargeType == null ? "": rechargeType);
query.executeUpdate();return "success";
}
(3)用JDBC方式连接数据库执行存储过程：
工具类：
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 classJdbcUtil {public staticConnection 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("连接失败");
}returndbConn;
}
}
调用方式：
@Overridepublic List getAllMonitorDatas(MapfilterMap)throwsException {
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= newDate();
}//为存储过程设置输入参数
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= newMonitorData();//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();
}returnlist;
}
4.用Criteria执行查询：
public Page find(Pagepage,
MapfilterMap) {
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);returnpages;
}catch(Exception e) {
e.printStackTrace();
}return null;
}
)
