java与数据库交互常用到的一些方法
2024-08-27 08:47:35
下面我整理了一下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;
}
最新文章
- 玩转github----1
- 国内CDN公共库
- IOS自定义仪表盘
- CSS里的引用@import、link
- 【Linux高频命令专题(3)】uniq
- POJ 1845 Sumdiv(因子分解+快速幂+二分求和)
- BZOJ 3631: [JLOI2014]松鼠的新家( 树链剖分 )
- js 面向对象选项卡
- java 图形界面
- chrome浏览器使用技巧
- Android数据库框架——GreenDao轻量级的对象关系映射框架，永久告别sqlite
- javascript---split 和 join 的区别
- 快速搭建一个直播Demo
- 彻底搞懂Scrapy的中间件（三）
- 为何CPU散片这么便宜？盒装CPU值得买吗
- [opencvjichu]cv::Mat::type() 返回值
- 理解Solr缓存及如何设置缓存大小
- Microsoft Visual C++ 14.0 is required. Get it with "Microsoft Visual C++ Build Tools"
- 关于运行“基于极限学习机ELM的人脸识别程序”代码犯下的一些错误
- Java NIO Socket编程实例