JAVA链接数据库的两种方式

1.直接采用JDBC方式进行链接

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


public class Test {
 //sql2005
    String driverClass="com.microsoft.sqlserver.jdbc.SQLServerDriver";//sql2000:com.microsoft.jdbc.sqlserver.SQLServerDriver
    String url="jdbc:sqlserver://localhost:1433;DatabaseName=bookstore";
    String user="sa";
    String password="sa";
    
    public Test(){
  try {
   Class.forName(driverClass);
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  }
    }
    
 public static void main(String[] args) {
  Test test = new Test();
        Connection conn=null;
        Statement stmt=null;
        ResultSet rs = null;
        try {
   conn=DriverManager.getConnection(test.url,test.user,test.password);
   stmt=conn.createStatement();
   rs = stmt.executeQuery("select * from bookinfo");
   while(rs.next()){
    System.out.println(rs.getString(2));
   }
  } catch (SQLException e) {
   e.printStackTrace();
  }finally{
   try {
    if(conn!=null){
     conn.close(); 
     conn = null;
    }
    if(stmt!=null){
     stmt.close();
     stmt = null;
    }
    if(rs!=null){
     rs.close();
     rs=null;
    }
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
 }
}

 

 2.第二种采用JNDI的方式

找到 tomcat的安装目录 D:\Tomcat\apache-tomcat-7.0.16\conf ，打开其中的Server.xml

Tomcat 中配置文件如下：

 <Context path="/test" docBase="D:\WorkSpace\MyEclipse\WebStore\WebRoot" reloadable="true">
  <Resource name="jdbc/bookstore" auth="Container" type="javax.sql.DataSource"
     maxActive="100" maxIdle="30" maxWait="10000"
                          username="sa" password="sa" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
      url="jdbc:sqlserver://localhost:1433;DatabaseName=bookstore"/>
 </Context>

将该元素放到最后<Host></Host>元素内

JAVA代码：

package com.store.bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.*;

public class BookDbBean {
	private DataSource ds = null;

	public BookDbBean() throws NamingException {
		Context ct = new InitialContext();
		ds = (DataSource) ct.lookup("java:comp/env/jdbc/bookstore");
	}

	public Connection getConnection() throws SQLException {
		return ds.getConnection();
	}

	protected void closeConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
				conn = null;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	protected void closeStatement(Statement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
				stmt = null;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
	}

	protected void closePreparedStatement(PreparedStatement pstmt) {
		if (pstmt != null) {
			try {
				pstmt.close();
				pstmt = null;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	protected void closeResultSet(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
				rs = null;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public Collection<BookBean> getBooks() {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<BookBean> bookList = new ArrayList<BookBean>();

		try {
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from bookinfo");
			while (rs.next()) {
				BookBean book = new BookBean(rs.getInt(1), rs.getString(2),
						rs.getString(3), rs.getString(4), rs.getString(5),
						rs.getFloat(6), rs.getInt(7), rs.getString(8));
				bookList.add(book);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeConnection(conn);
			closeStatement(stmt);
			closeResultSet(rs);
		}
		return bookList;
	}

	public BookBean getBook(int bookId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		BookBean book = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement("select * from bookinfo where id = ?");
			pstmt.setInt(1, bookId);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				 book = new BookBean(rs.getInt(1), rs.getString(2),
							rs.getString(3), rs.getString(4), rs.getString(5),
							rs.getFloat(6), rs.getInt(7), rs.getString(8));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeConnection(conn);
			closePreparedStatement(pstmt);
			closeResultSet(rs);
		}
		return book;
	}
	
	public Collection<BookBean> searchBook(String keyWord){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<BookBean> bookList = new ArrayList<BookBean>();
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from bookinfo where title like '%"+keyWord+"%'");
			while(rs.next()){
				BookBean book = new BookBean(rs.getInt(1), rs.getString(2),
						rs.getString(3), rs.getString(4), rs.getString(5),
						rs.getFloat(6), rs.getInt(7), rs.getString(8));
				bookList.add(book);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeConnection(conn);
			closeStatement(stmt);
			closeResultSet(rs);
		}
		return bookList;
	}
	
	public boolean isAmountEnough(int bookId,int num){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		boolean bEnough = false;
		
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select amount from bookinfo where id = "+bookId);
			if(rs.next()){
				int amount = rs.getInt(1);
				bEnough = amount>=num?true:false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeConnection(conn);
			closeStatement(stmt);
			closeResultSet(rs);
		}
		return bEnough;
	}
	public synchronized void buyBooks(CartBean  cart){
		Connection conn = null;
		PreparedStatement pstmt = null;
		Iterator<CartItemBean> it = cart.getItems().iterator();

		try {
			conn = getConnection();
			pstmt = conn.prepareStatement("update bookinfo set amount = amount-? where id=?");
			
			while(it.hasNext()){
				CartItemBean cartItem = it.next();
				BookBean book = cartItem.getBook();
				pstmt.setInt(1, cartItem.getNum());
				pstmt.setInt(2, book.getId());
				
				pstmt.addBatch();
			}
			pstmt.executeBatch();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeConnection(conn);
			closePreparedStatement(pstmt);
		}
		
	}
	
}