jdbcutil.java

来自「数据库远程同步软件NetBeans项目源文件 项目采用Jdesktop集成组件」· Java 代码 · 共 180 行

package com.qixuan.jdbc.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

/***************************
 * 这是一个JDBC的实效类，它提供了
 * 释放JDBC资源，动态获取主键，分析结果集，
 * 分析批处理的结果,..等相关操作
 * @author yejf
 *
 */
public class JdbcUtil {

	/*************************
	 * 释放JDBC 相关的资源
	 * @param con	连接
	 * @param stm	Statement
	 * @param rs	结果集
	 */
	public static void release(
					Connection con,
					Statement stm,
					ResultSet rs) {
		try {
			if(rs != null)	
				rs.close();
			if(stm != null)	
				stm.close();
			if(con != null)	
				con.close();
			
		} catch (SQLException ee) {
			ee.printStackTrace();
		}
	}
	
	public static void printRS(ResultSet rs) 
							throws SQLException {
		//获取元数据
		ResultSetMetaData rsmd = rs.getMetaData();
		// 获取此结果集的列数
		int columnCount = rsmd.getColumnCount();
		//打印出头部
		for(int i=1;i<=columnCount;i++) {
			String columnLabel = rsmd
							.getColumnLabel(i);
			System.out.print(columnLabel+"\t");
		}
		//换行
		System.out.println();
		System.out.println("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-===-=-=-");
		//输出结果集本身
		while(rs.next()) {
			for(int i=1;i<=columnCount;i++) {
				System.out.print(rs.getObject(i)+"\t");
			}
			//换行
			System.out.println();
		}
		
	}
	
	/*************************************
	 * create sequence hibernate_sequence;
	 * @param seqName
	 * @return
	 */
	public static int getPKBySeq(String seqName) {
		int pk = -1;
		String seq = null;
		if(seqName == null || seqName.trim().length() <= 0){
			seq = "hibernate_sequence";
		} else {
			seq = seqName;
		}
		String sql = "select "+seq+".nextval from dual";
		Connection con = null;
		Statement stm = null;
		ResultSet rs = null;
		try {
			con = ConnectionFactory.getConnection();
			stm = con.createStatement();
			rs = stm.executeQuery(sql);
			if(rs.next()) {
				pk = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.release(con, stm, rs);
		}
		
		return pk;
	}
	
	/*********************************
	 * 作业：请实现此方法；
	 * 要求：通过一个单行单列的表来获取主键值;
	 * 如：create table id_table(
	 * 		next_id number
	 * 	   );
	 *     insert into id_table values(1);
	 *     commit;
	 *  注： 
	 *  每取一次，都要把此列＋１并更新回去
	 *  //查询ＳＱＬ
	 *  String sql = "select next_id from id_table for update";
	 *  //更新ＳＱＬ
	 *  String sql = "update id_table set next_id = next_id + 1";
	 * @param tableName  表名
	 * @param columnName  列名
	 * @return
	 */
	public static int getPKByTable(
				String tableName,
				String columnName) {
		
		int pk = -1;				
		Connection con = null;
		PreparedStatement pstm= null;
		Statement stm = null;
		ResultSet rs = null;
		 try {
			con=ConnectionFactory.getConnection();
			String sql = "select "+columnName.toLowerCase()+" from "+tableName+" for update ";			
			stm = con.createStatement();			
			rs = stm.executeQuery(sql);
			if(rs.next()) {
				pk = rs.getInt(1);
			}				
			sql="update "+tableName+" set "+columnName.toLowerCase()+" = ?  ";
			pstm=con.prepareStatement(sql);				
			int next_pk=pk;
			pstm.setInt(1, ++next_pk);
			pstm.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block			
			e.printStackTrace();
		}finally {
			JdbcUtil.release(con, pstm, rs);
			JdbcUtil.release(null, stm, null);
		}
		 
		return pk;
	}
	
	
	/***************************
	 * 分析批处理的结果集
	 */
	public static void analysisBatchRS
						(int[] results) {
//		分析批处理的结果
		for(int i=0;i<results.length;i++) {
			switch (results[i]) {
			case Statement.SUCCESS_NO_INFO:
				System.out.println("-- SQL 执行成功，但受影响的记录数未知...");
				break;
			case Statement.EXECUTE_FAILED:
				System.out.println("-- 本SQL命令执行失败，并且驱动会继续执行批中剩下的SQL命令....");
				break;
			default:
				System.out.println("-- SQL 执行成功，本命令影响的记录数为："+results[i]);
			}
		}
	}
}