下面我整理了一下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;
}
)
