JDBC最基本CRUD操作工具类

以连接SqlServer2008 R2为例 

import java.sql.*;

public final class JdbcUtils {
	private String  url;
	private String	user;
	private String	password;
	
	
	public JdbcUtils(String url,String user,String password){
		this.url = url;
		this.user = user;
		this.password = password;
	}
	
	static{
		try {
			//加载驱动,原理是反射
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            //Class.forName("oracle.jdbc.driver.OracleDriver");// 加载Oracle驱动程序			               
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/***
	 * 根据SQL进行查询操作
	 * @param sql
	 * @throws SQLException
	 */
	public void queryResult(String sql) throws SQLException{
		
		try
		(
			//获取数据库链接
			Connection conn = DriverManager.getConnection(url, user, password);
			//使用Connection创建一个Statement对象
			Statement stmt = conn.createStatement();
			//执行sql语句,返回查询结果集
			ResultSet rs = stmt.executeQuery(sql))
		{			
			while(rs.next()){
				System.out.println("Id=" + rs.getLong(1) + ";ProductName=" + rs.getString(2));
			}
		}
		
	}
	
	/***
	 * 根据SQL进行update操作
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public int updateResult(String sql) throws SQLException{
		
		try
		(
			//获取数据库链接
			Connection conn = DriverManager.getConnection(url, user, password);
			//使用Connection创建一个Statement对象	
			Statement stmt = conn.createStatement())
		{
			//返回执行sql的行数
			return stmt.executeUpdate(sql);
		}
	}
	
	/**
	 * 使用execute方法执行SQL语句
	 * @param sql
	 * @throws SQLException
	 */
	public void executeSql(String sql) throws SQLException{
		try
		(
			Connection conn = DriverManager.getConnection(url, user, password);
			Statement stmt = conn.createStatement())
		{
			//判断执行后是否有结果集合
			boolean hasResultSet = stmt.execute(sql);
			if(hasResultSet){
				ResultSet rs = stmt.getResultSet();
				ResultSetMetaData rsmdData = rs.getMetaData();
				int columnCount = rsmdData.getColumnCount();
				while(rs.next())
				{
					for (int i = 0; i < columnCount; i++) {
						System.out.print(rs.getString(i + 1) + "\t");
					}
					System.out.print("\n");
				}
			}
			else {
				//返回该SQL影响的行数
				int count = stmt.getUpdateCount();
				System.out.println("受影响的行数是" + count);
			}
		}
	}
}

 

使用方法 

	public static void main(String[] args) {
		String url = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=DailyProductionDB";
        //String oracleUrl = jdbc:oracle:thin:@127.0.0.1:1521:ecology
		String user = "sa";
		String password = "123";
		
		JdbcUtils cs = new JdbcUtils(url, user, password);
		try {
			cs.queryResult("SELECT * FROM dbo.Product");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}