使用JDBC连接SqlServer

2019-08-13

1.SQL Server 的配置信息

private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
 private static final String URL = "jdbc:sqlserver://127.0.0.1:1433;databaseName=studentDB";
 private static final String USERNAME = "sa";
 private static final String PASSWORD = ".";

sqlserver驱动下载:sqljdbc2008.jar;

2.MySql 的配置信息

private static final String DRIVER = "com.mysql.jdbc.Driver";
 private static final String URL = "jdbc:mysql://localhost:3306/person?useUniCode=true&characterEncoding=utf-8";
 private static final String USERNAME = "root";
 private static final String PASSWORD = "123";

mysql驱动下载:mysql-connector-java-5.1.18-bin.jar;

3.附上basedao.class文件

package com.person.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BaseDao {
	/**
	 * 閺佺増宓佹惔鎾圭箾閹恒儱鐡х粭锔胯
	 */
	private static final String DRIVER = "com.mysql.jdbc.Driver";
	private static final String URL = "jdbc:mysql://localhost:3306/person?useUniCode=true&characterEncoding=utf-8";
	private static final String USERNAME = "root";
	private static final String PASSWORD = "123";

	private Connection conn;
	private PreparedStatement statement;
	private ResultSet rs;
	/**
	 * 閼惧嘲褰囨潻鐐村复閻ㄥ嫭鏌熷▔锟?
	 */
	public Connection getConnection() {
		try {
			Class.forName(DRIVER);
			//閸掋倖鏌囨潻鐐村复鐎电钖勯弰顖氭儊娑撹櫣鈹栭幋鏍拷鍛秼閸撳秷绻涢幒銉ヮ嚠鐠炩剝妲搁崥锔芥ЦisClosed()瀹歌尙绮¢崗鎶芥4閻拷
			if(conn==null||conn.isClosed()){
				conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
			}
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return conn;

	}
	/*public Connection getConnection() {
		try {
			//濮濄儵顎冩稉锟介敍姘讹拷姘崇箖Context閼惧嘲褰囬弫鐗堝祦濠э拷
			Context context=new InitialContext();
			//濮濄儵顎冩禍宀嬬窗閼惧嘲褰囬弫鐗堝祦濠э拷
		    DataSource dataSource=(DataSource)context.lookup("java:comp/env/jdbc/news");
		    try {
		    	//濮濄儵顎冩稉澶涚窗閼惧嘲褰囬柧鐐复
		    	if(conn==null||conn.isClosed()){
		    		conn=dataSource.getConnection();
		    	}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (NamingException e) {
			e.printStackTrace();
		}
		return conn;
	}
	*/

	/**
	 * 婢х偛鍨归弨锟?
	 * 
	 * @throws Exception
	 */
	public int executeUpdate(String sql, Object... obj) throws Exception {
		// 閼惧嘲褰囨潻鐐村复
		getConnection();
		// 閼惧嘲褰嘝reparedStatement鐎电钖?
		
		statement = conn.prepareStatement(sql);
		// 瀵邦亞骞嗛崝鐘烘祰閸欏倹鏆?
		for (int i = 1; i <= obj.length; i++) {
			statement.setObject(i, obj[i-1]);
		}
		// 閹笛嗩攽SQL
		int count = statement.executeUpdate();

		return count;
	}

	/**
	 * 閺屻儴顕?
	 * @throws SQLException 
	 */
	public ResultSet executeQuery(String sql, Object... obj) throws Exception {
		// 閼惧嘲褰囨潻鐐村复
		getConnection();
		// 閼惧嘲褰嘝reparedStatement鐎电钖?
		statement = conn.prepareStatement(sql);
		// 瀵邦亞骞嗛崝鐘烘祰閸欏倹鏆?
		for (int i = 1; i <= obj.length; i++) {
			
			statement.setObject(i, obj[i-1]);
		}
		rs = statement.executeQuery();
		return rs;
	}

	/**
	 * 閸忔娊妫存潻鐐村复
	 * @throws Exception 
	 */
	public void closeResource() throws Exception {
		if(rs!=null){
			rs.close();
		}
		if(statement!=null){
			statement.close();
		}
		if(conn!=null){
			//閸忔娊妫存潻鐐村复
			conn.close();
		}
	}
}