sqlserver存储过程、触发器编写和调用

最近做 了一个小demo，数据库采用的是sqlserver。需要用jdbc调用存储过程。

1、触发器

先上sql

-- 建退票表 CREATE TABLE [dbo].[unsubscribe] (  [id] int  IDENTITY(1,1) NOT NULL,  [ticket_number] varchar(50) COLLATE Chinese_PRC_CI_AS  NOT NULL,  [time] datetime  NOT NULL,  CONSTRAINT [PK__lefts__3213E83F1DE57479] PRIMARY KEY CLUSTERED ([id])WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  ON [PRIMARY])  ON [PRIMARY]GOALTER TABLE [dbo].[unsubscribe] SET (LOCK_ESCALATION = TABLE)GO-- 触发器CREATE TRIGGER [dbo].[tri_unsubscribe]ON [dbo].[unsubscribe]WITH EXECUTE AS CALLERFOR INSERTASbegindeclare @tc_num char(20)select @tc_num=[ticket_number] from insertedupdate ticketset [left_count]=[left_count]+1where number=@tc_numendGO

触发器：往退票表中插入数据时，需要将余票数+1

2、存储过程

-- 创建指定发车时间和车次的车票销售情况的存储过程-- 数据库名：ticketsuse tickets if exists    (select name from sysobjects where name = 'checksells' and type = 'p' )  drop procedure checksells   go-- 创建一个名为checksells的存储过程create procedure checksells@trainName varchar(30),@startTime datetime  as  beginselect sell.id,sell.train_name,sell.user_number,ticket.start_station,ticket.end_station,ticket.number,ticket.price,ticket.time from sell left join ticket on ticket.number=sell.ticket_number where ticket.train_name=@trainName and CONVERT(varchar(100),ticket.time,0)=CONVERT(varchar(100),@startTime,0)endgo-- 测试执行exec  checksells 'D1050','2019-06-21 04:12:22.123'   go

存储过程：带两个入参的存储过程

3、jdbc调用

/**	 * 存储过程调用获取销售情况	 */	@Override	public List<SellVO> listSellByCall(String trainName, Date startTime) {		String sql = "{call checksells(?,?)}";		Connection conn = null;		PreparedStatement pst = null;		ResultSet rs = null;		List<SellVO> voList = new ArrayList<SellVO>();		try {			conn = JDBCUtils.getConnection();			pst = conn.prepareCall(sql);			pst.setString(1, trainName);			pst.setTimestamp(2, new Timestamp(startTime.getTime()));			rs = pst.executeQuery();			while (rs.next()) {				SellVO vo = new SellVO();				vo.setId(rs.getInt("id"));				vo.setTrainName(rs.getString("train_name"));				vo.setTicketNumber(rs.getString("user_number"));				vo.setStartStation(rs.getString("start_station"));				vo.setEndStation(rs.getString("end_station"));				vo.setTicketNumber(rs.getString("number"));				vo.setPrice(rs.getInt("price"));				vo.setTime(rs.getTimestamp("time"));				voList.add(vo);			}		} catch (SQLException e) {			e.printStackTrace();		} finally {			JDBCUtils.close(conn, pst, rs);		}		return voList;	}

JDBCUtils为连接数据库的工具类

import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class JDBCUtils {	private static String url;	private static String user;	private static String password;	static {		try {			String driverClassName = PropertyHelper.get("driverClassName");			Class.forName(driverClassName);			url = PropertyHelper.get("url");			user = PropertyHelper.get("user");			password = PropertyHelper.get("password");		} catch (Exception e) {			e.printStackTrace();		}	}	public static Connection getConnection() {		Connection conn = null;		try {			conn = DriverManager.getConnection(url, user, password);		} catch (SQLException e) {			e.printStackTrace();		}		return conn;	}	public static void close(Connection conn, Statement stat, ResultSet rs) {		close(conn, stat);		if (rs != null) {			try {				rs.close();			} catch (SQLException e) {				e.printStackTrace();			}		}	}	public static void close(Connection conn, Statement stat) {		if (conn != null) {			try {				conn.close();			} catch (SQLException e) {				e.printStackTrace();			}		}		if (stat != null) {			try {				stat.close();			} catch (SQLException e) {				e.printStackTrace();			}		}	}	public static void close(Connection conn, PreparedStatement stat) {		if (conn != null) {			try {				conn.close();			} catch (SQLException e) {				e.printStackTrace();			}		}		if (stat != null) {			try {				stat.close();			} catch (SQLException e) {				e.printStackTrace();			}		}	}}