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

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

}