连接MySql和SQLServer数据库

java中连接MySql和SQLServer数据库

/**
* 书写数据库连接基类
*/
public class ConnectionManager {
	/**
	 * MYSQL的连接变量
	 */
	private static final String CLS = "com.mysql.jdbc.Driver";
	private static final String URL = "jdbc:mysql://localhost:3306/bookdb";
	private static final String USER = "root";
	private static final String PWD = "123456";
	/**
	 * SQLSERVER 的连接变量
	 */
	/*
	 * private static final String
	 * CLS="com.microsoft.sqlserver.jdbc.SQLServerDriver"; private static final
	 * String URL="jdbc:sqlserver://localhost:1433;DatabaseName=bookdb"; private
	 * static final String USER="sa"; private static final String PWD="123456";
	 */

	public static Connection conn = null;
	public static Statement stmt = null;
	public static PreparedStatement pStmt = null;
	public static ResultSet rs = null;

	/**
	 * 取得连接的方法
	 */
	public static void getConnection() {
		try {
			Class.forName(CLS);
			conn = DriverManager.getConnection(URL, USER, PWD);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 关闭所有的数据库操作对象
	 */
	public static void closeAll() {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			if (pStmt != null) {
				pStmt.close();
				pStmt = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

2，读取数据库中的信息

/**
* 实体类
*/
public class Book implements Serializable{
	private String isbn;
	public String getIsbn() {
		return isbn;
	}
	public void setIsbn(String isbn) {
		this.isbn = isbn;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public int getEditionNumber() {
		return editionNumber;
	}
	public void setEditionNumber(int editionNumber) {
		this.editionNumber = editionNumber;
	}
	public String getCopyright() {
		return copyright;
	}
	public void setCopyright(String copyright) {
		this.copyright = copyright;
	}
	public int getPublisherID() {
		return publisherID;
	}
	public void setPublisherID(int publisherID) {
		this.publisherID = publisherID;
	}
	public String getImageFile() {
		return imageFile;
	}
	public void setImageFile(String imageFile) {
		this.imageFile = imageFile;
	}
	public double getPrice() {
		return price;
	}
	public void setPrice(double price) {
		this.price = price;
	}
	private String title;
	private int editionNumber;
	private String copyright;
	private int publisherID;
	private String imageFile;
	private double price;
}

/**
 * 实体类的操作类
 *
 */
public class BookDAO extends ConnectionManager {
	/**
	 * 取得所有书籍的信息列表的集合
	 */
	public List<Book> getAll(){
		List<Book> list=new ArrayList<Book>();
		getConnection();//取得连接
		String sql="select * from books";//sql语句
		try {
			pStmt=conn.prepareStatement(sql);//提交语句
			rs=pStmt.executeQuery();//执行sql语句并将其返回结果给ResultSet对象rs
			while(rs.next()){//如果有数据则将每一行的数据保存至实体类中，因为数据库中的一行数据对应一个实体类的对象
				Book b=new Book();
				b.setIsbn(rs.getString("isbn"));//rs.getString("isbn")得到ResultSet集合中key值为isbn的value值，
                                                                //并将它赋给book的对象b的isbn属性 ,下同
				b.setTitle(rs.getString("title"));
				b.setEditionNumber(rs.getInt("editionNumber"));
				b.setCopyright(rs.getString("copyRight"));
				b.setPublisherID(rs.getInt("publisherId"));
				b.setPrice(rs.getDouble("price"));
				b.setImageFile(rs.getString("imageFile"));
				list.add(b);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll();
		}
		
		return list;
	}
/**
	 * 取得书籍集合信息结果的方法
	 * @return
	 */
	public Result getBookResult(){
		Result result=null;
		getConnection();
		String sql="select * from books";
		try {
			pStmt=conn.prepareStatement(sql);
			rs=pStmt.executeQuery();
			//将ResultSet转换为Result
			result=ResultSupport.toResult(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll();
		}
		return result;
	}
}

3、对应数据库(bookdb)中的表(books)