数据源连接SQL server

 

model:

package model;



public class UserInfo {
	private int id;
	 
	private String userName;
	private String address;
	 

	
	
	public UserInfo(String userName, String address) {
		super();
		this.userName = userName;
		this.address = address;
	}


	public UserInfo(int id, String userName, String address) {
		super();
		this.id = id;
		this.userName = userName;
		this.address = address;
	}


	public UserInfo() {
		super();
	}


	public int getId() {
		return id;
	}


	public void setId(int id) {
		this.id = id;
	}


	public String getUserName() {
		return userName;
	}


	public void setUserName(String userName) {
		this.userName = userName;
	}


	public String getAddress() {
		return address;
	}


	public void setAddress(String address) {
		this.address = address;
	}

	
}

 

 

dao:

package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import commons.DbManager;

import model.UserInfo;

public class UserDao {
	public int create(UserInfo userInfo) {
		Connection conn = null;
		PreparedStatement pst = null;
		int state = 0;

		try {
			conn = DbManager.getConnection();
			conn.setAutoCommit(false);
			pst = conn
					.prepareStatement("insert into jdbc values(?,?,)");
			pst.setString(1, userInfo.getUserName());
			pst.setString(2, userInfo.getAddress());
			 
			state = pst.executeUpdate();
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			DbManager.clossConnection(conn, pst, null);
		}
		return state;
	}

	public List<UserInfo> findByAccount(UserInfo userInfo) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		List<UserInfo> list = new ArrayList<UserInfo>();
		try {
			conn = DbManager.getConnection();
			conn.setAutoCommit(false);
			pst = conn
					.prepareStatement("select * from jdbc where address=?");
			pst.setString(1, userInfo.getAddress());

			rs = pst.executeQuery();
			while (rs.next()) {
				list.add(new UserInfo(rs.getInt(1), rs.getString(2), rs
						.getString(3)));
			}
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			DbManager.clossConnection(conn, pst, rs);
		}
		return list;
	}
}

 

 

commons:

package commons;



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

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

public class DbManager {
	public static Connection getConnection() {
		Connection conn = null;

		try {
			Context context = new InitialContext();
			DataSource ds = (DataSource) context
					.lookup("java:comp/env/jdbc/myinfo");
			conn = ds.getConnection();

		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return conn;

	}

	public static void clossConnection(Connection conn, PreparedStatement pst,
			ResultSet rs) {

		try {
			if (rs != null){
				rs.close();
			}
			if (pst !=null){
				pst.close();
			}
			if (conn!= null){
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}

 

 

数据源的xml配置文件：

<Context>
<Resource name="jdbc/myinfo" auth="Container"
	type="javax.sql.DataSource" maxActive="100" maxIdle="30"
	maxWait="10000" username="sa" password="wdpc"
	driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
	url="jdbc:sqlserver://localhost:1433;DatabaseName=test;user=sa;password=wdpc" />
</Context>

 

 

context.xml放在