Javaweb JDBC连接数据库

＃＃创建数据库

＃＃连接SQL数据库

public class DBUtil {
	private static String DRI="com.microsoft.sqlserver.jdbc.SQLServerDriver";
	public static Connection connectDB()throws Exception{
		Class.forName(DRI);
		Connection conn=DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=student","sa","080105");
		System.out .println("链接成功");
		return conn;
	}		
}

链接SQL数据库时，需在创建的工程中导入SQL的jdbc
方法：Build　Path－＞Add　Jars
选择下好的jar文件。
＃＃封装

public class UserVO {
private int id;
private String userName;
private int age;
private int sex;
private Date createDt;

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 int getAge() {
	return age;
}
public void setAge(int age) {
	this.age=age;
}

public int getSex() {
	return sex;
}
public void setSex(int sex) {
	this.sex=sex;
}

public Date getCreateDt() {
	return createDt;
}
public void setCreateDt(Date createDt) {
	this.createDt=createDt;
}

public String toString() {
	return"UserVo[id="+id+"userName="+userName+",age="+age+",sex="+sex+",createDt="+createDt+"]";
}

}

＃＃对数据库进行查增删改

public class UserDao {
	public List<UserVO>queryAll()throws Exception{
		Connection conn=DBUtil.connectDB();
		String sql="SELECT*FROM tbl_use_info";
		List<UserVO>userList=new ArrayList<UserVO>();
		Statement stmt=conn.createStatement();
		ResultSet rs=stmt.executeQuery(sql);
		while(rs.next()) {
			UserVO user =new UserVO();
			user.setId(rs.getInt("id"));
			user.setUserName(rs.getString("user_name"));
			user.setAge(rs.getInt("age"));
			user.setSex(rs.getInt("sex"));
			user.setCreateDt(rs.getDate("create_dt"));
			userList.add(user);
		}
		return userList;
	}
	
	public List<UserVO>querryByParams(List<Map<String,Object>>params)throws Exception{
		Connection conn=DBUtil.connectDB();
		StringBuilder sql=new StringBuilder("SELECT * FROM tbl_use_info WHERE 1=1 ");
		for(Map<String,Object>param:params) {
			sql.append(" and ");
			sql.append(" "+param.get("col")+" ");
			sql.append(" "+param.get("rel")+" ");
			sql.append(" "+param.get("value")+" ");
		}
		System.out.println(sql.toString());
		List<UserVO>userList=new ArrayList<UserVO>();
		Statement stmt=conn.createStatement();
		ResultSet rs=stmt.executeQuery(sql.toString());
		while(rs.next()) {
			UserVO user=new UserVO();
			user.setId(rs.getInt("id"));
			user.setUserName(rs.getString("user_name"));
			user.setAge(rs.getInt("age"));
			user.setSex(rs.getInt("sex"));
			user.setCreateDt(rs.getDate("create_dt"));
			userList.add(user);
		}
		return userList;
	}
	public void addUser(UserVO user)throws Exception{
		Connection conn=DBUtil.connectDB();
		String sql="INSERT INTO tbl_use_info(user_name,age,sex,create_dt)"+"VALUES(?,?,?,?)";
		PreparedStatement pstmt=conn.prepareStatement(sql);
		pstmt.setString(1,user.getUserName());
		pstmt.setInt(2, user.getAge());
		pstmt.setInt(3, user.getSex());
		pstmt.setDate(4,new Date(new java.util.Date().getTime()));
		pstmt.execute();
		
	}
	public void daleteUser(int id)throws Exception{
		Connection conn=DBUtil.connectDB();
		String sql="DELETE FROM tbl_use_info WHERE id=?";
		PreparedStatement pstmt=conn.prepareStatement(sql);
		pstmt.setInt(1, id);
		pstmt.execute();
	}
	public void updateUser(UserVO user)throws Exception{
		Connection conn=DBUtil.connectDB();
		String sql="UPDATE tbl_use_info SET user_name=?,age=?,sex=?"+" WHERE id=?";
		PreparedStatement pstmt=conn.prepareStatement(sql);
		pstmt.setString(1,user.getUserName());
		pstmt.setInt(2, user.getAge());
		pstmt.setInt(3, user.getSex());
		pstmt.setInt(4, user.getId());
		pstmt.executeUpdate();
	}
}

＃＃测试

public class test {

	public static void main(String[] args) {
		UserDao dao=new UserDao();
		UserVO user=new UserVO();
		user.setUserName("Jack");
		user.setAge(70);
		user.setSex(1);
		user.setId(2);
		try {
			dao.updateUser(user);
			List<UserVO>userList=dao.queryAll();
			for(UserVO users:userList)
				System.out.println(users);
		}catch(Exception e) {
			e.printStackTrace();
		}
			
	}
	public void delete() {
		UserDao dao=new UserDao();
		try {
			dao.daleteUser(3);
			List<UserVO>userList=dao.queryAll();
			for(UserVO user:userList)
				System.out.println(user);
		}catch(Exception e) {
			e.printStackTrace();
		}
			
	}
	
	
	public void add() {
		UserDao dao=new UserDao();
		UserVO user=new UserVO();
		user.setUserName("小明");
		user.setAge(31);
		user.setSex(1);
		try {
			dao.addUser(user);
			List<UserVO>userList=dao.queryAll();
			for(UserVO users:userList)
				System.out.println(users);
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	
	public void select1() {
		UserDao dao=new UserDao();
		Map<String,Object>param1=new HashMap<String,Object>();
		List<Map<String,Object>>params=new ArrayList<Map<String,Object>>();
		param1.put("col","user_name");
		param1.put("rel", "like");
		param1.put("value", "'%大白%'");
		params.add(param1);
		Map<String,Object>param2=new HashMap<String,Object>();
		param2.put("col", "sex");
		param2.put("rel", "=");
		param2.put("value",0);
		params.add(param2);
		try {
			List<UserVO>userList= dao.querryByParams(params);
			for(UserVO user:userList) {
				System.out.println(user);
				}
		}catch(Exception e) {
			e.printStackTrace();
		}
			
	}
	
public void selectall() {
		UserDao dao=new UserDao();
		try {
			List<UserVO>userList=dao.queryAll();
			for(UserVO user:userList)
				System.out.println(user);
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}