利用JSP来开发Android服务端短学期的学习总结

第一步先把数据库连接通

有两种方式可以用

第一在代码中实现

 

package com.nit.util.bean;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.util.Properties;



public class Dbutil {

	private  static Connection con;
	public  static Connection getConnection(){
		
		
		
		
		String driver= "com.mysql.jdbc.Driver";
		String url= "jdbc:mysql://localhost:3306/nba";
		String user= "root";
		String pwd= "123456";
		
	
		Connection con = null;
		try{
//			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Class.forName(driver);
			
			try {
				con = DriverManager.getConnection(url,user,pwd);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			System.out.println(con);
			return con;
		}catch(ClassNotFoundException e){
//			 e.getMessage();
			e.printStackTrace();
			return null;
		}
		
		
	}
	
	/*
	 * 关闭资源
	 */
	
	public static void  closeAl(Connection con,PreparedStatement prestmt,ResultSet rs){
		try {
			if(rs!=null)
				rs.close();
			if(prestmt!=null)
				prestmt.close();
			if(con!=null)
				con.close();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	
	
	public static void main(String[] args){
		Dbutil du = new Dbutil();
		du.getConnection();
	}
}

 

第二利用资源文件

 

package com.mjm.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBUtil {

	public void closeConnection(Connection conn) {
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public Connection openConnection() {
		Properties prop = new Properties();

		String driver = null;
		String url = null;

		String username = null;
		String password = null;

		try {
			prop.load(this.getClass().getClassLoader().getResourceAsStream(
					"DBConfig.properties"));

			driver = prop.getProperty("driver");
			url = prop.getProperty("url");
			username = prop.getProperty("username");
			password = prop.getProperty("password");

			Class.forName(driver);

			return DriverManager.getConnection(url, username, password);

		} catch (Exception e) {
			System.out.println("连接数据库异常！");
			e.printStackTrace();
		}
		return null;

	}
}

 

第二步建立相应的javabean

这里以userbean为例

 

 

package com.mjm.bean;

public class User {
	private int u_Id;
	private String u_Account;
	private String u_PassWord;
	private String u_Name;
	private int u_Permission;
	private int u_Sex;
	private String u_contact;
	private String u_remark;
	public int getUserId() {
		return u_Id;
	}
	public void setUserId(int u_Id) {
		this.u_Id = u_Id;
	}
	public String getAccount() {
		return u_Account;
	}
	public void setAccount(String u_Account) {
		this.u_Account = u_Account;
	}
	public String getPassword() {
		return u_PassWord;
	}
	public void setPassword(String u_PassWord) {
		this.u_PassWord = u_PassWord;
	}
	public String getName() {
		return u_Name;
	}
	public void setName(String u_Name) {
		this.u_Name = u_Name;
	}
	public int getPermission() {
		return u_Permission;
	}
	public void setPermission(int u_Permission) {
		this.u_Permission = u_Permission;
	}
	public int getGender() {
		return u_Sex;
	}
	public void setGender(int u_Sex) {
		this.u_Sex = u_Sex;
	}
	public String getContact() {
		return u_contact;
	}
	public void setContact(String u_contact) {
		this.u_contact = u_contact;
	}
	public String getRemark() {
		return u_remark;
	}
	public void setRemark(String u_remark) {
		this.u_remark = u_remark;
	}
	
	
}

第三步，建立以constant包的接口来方便在引用javabean里面的属性

 

package com.mjm.constant;

public interface DB {
	String DBNAME = "addressbook";

	interface Tables {

		interface User {
			String TABLENAME = "users";

			interface Fields {
				String USERID  = "u_Id";
				String ACCOUNT  = "u_Account";
				String PASSWORD  = "u_PassWord";
				String NAME  = "u_Name";
				String GENDER  = "u_Sex";
				String PERMISSION  = "u_Permission";
				String CONTACT  = "u_contact";
				String REMARK  = "u_remark";
			}
		}
		
	}
}

第四部建立对应javabean里面的Dao和DaoImpl类

还是以userDao和userDaoImpl为例

 

package com.mjm.dao;

import java.util.List;

import com.mjm.bean.User;


public interface UserDao {
	/*
	 * 用户登录
	 */
	public boolean login(String username, String password);

	/*
	 * 得到用户信息
	 */
	public List<User> getAllUser();

	/*
	 * 注册
	 */
	public boolean userRegister(User user);
	/*
	 * 删除用户
	 */
	public boolean deleteUserById(int userId);
	/*
	 * 更新用户
	 */
	public boolean updateUser(User user);
	
	public boolean checkAccountIsExists(String account);
	
	public User getUserByAccount(String account);
}

 

package com.mjm.dao.impl;

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 com.mjm.bean.User;
import com.mjm.dao.UserDao;
import com.mjm.util.DBUtil;
import com.mjm.constant.DB.Tables;

public class UserDaoImpl implements UserDao {
	/**
	 * 登入验证
	 * 
	 * @param account
	 *            登入账户
	 * @param password
	 *            登入密码
	 * @return true：成功 false：失败
	 */
	public boolean login(String account, String password) {

		
		String sql = "select count(*) result from " + Tables.User.TABLENAME
				+ " where " + Tables.User.Fields.ACCOUNT + " =? and "
				+ Tables.User.Fields.PASSWORD + " =? ";
		DBUtil util = new DBUtil();
		Connection conn = util.openConnection();

		try {
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setString(1, account);
			statement.setString(2, password);
			ResultSet rs = statement.executeQuery();
			if (rs.next()) {
				int result = rs.getInt("result");
				if (result > 0)
					return true;
			}
		} catch (SQLException e) {
			System.out.println("sql异常！");
			System.out.println("sql login-->" + sql);
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * 获取所有联系信息
	 * 
	 * @return 所有联系人集合
	 */
	public List<User> getAllUser() {
		List<User> users = null;
		String sql = "select * from " + Tables.User.TABLENAME + " where "
				+ Tables.User.Fields.PERMISSION + " <>0";
		DBUtil util = new DBUtil();
		Connection conn = util.openConnection();
		try {
			PreparedStatement prep = conn.prepareStatement(sql);

			ResultSet rs = prep.executeQuery();
			users = new ArrayList<User>();
			while (rs.next()) {
				User user = getUserFromResultSet(rs);
				users.add(user);
			}
		} catch (SQLException e) {
			System.out.println("sql异常！");
			System.out.println("sql getAllUser-->" + sql);
			e.printStackTrace();
		}
		return users;
	}

	/**
	 * 注册用户
	 * 
	 * @param user
	 *            用户信息
	 * @return true：注册成功 false：注册失败
	 */
	public boolean userRegister(User user) {
		String sql = "insert into " + Tables.User.TABLENAME + " ( "
				+ Tables.User.Fields.ACCOUNT + ","
				+ Tables.User.Fields.PASSWORD + "," + Tables.User.Fields.NAME
				+ "," + Tables.User.Fields.GENDER + ","
				+ Tables.User.Fields.PERMISSION + ","
				+ Tables.User.Fields.CONTACT + "," + Tables.User.Fields.REMARK
				+ " ) values(?,?,?,?,?,?,?)";
		DBUtil util = new DBUtil();
		Connection conn = util.openConnection();
		PreparedStatement prep;
		try {
			prep = conn.prepareStatement(sql);
			prep.setString(1, user.getAccount());
			prep.setString(2, user.getPassword());
			prep.setString(3, user.getName());
			prep.setInt(4, user.getGender());
			prep.setInt(5, user.getPermission());
			prep.setString(6, user.getContact());
			prep.setString(7, user.getRemark());
			prep.execute();
			return true;
		} catch (SQLException e) {
			System.out.println("sql异常！");
			System.out.println("sql userRegister-->" + sql);
			e.printStackTrace();
		}
		return false;
	}

	public boolean updateUser(User user) {
		String sql = "update " + Tables.User.TABLENAME + " set "
				+ Tables.User.Fields.ACCOUNT + "='" + user.getAccount() + "',"
				+ Tables.User.Fields.PASSWORD + "='" + user.getPassword() + "',"
				+ Tables.User.Fields.NAME + "='" + user.getName() + "',"
				+ Tables.User.Fields.GENDER + "=" + user.getGender() + ","
				+ Tables.User.Fields.PERMISSION + "=" + user.getPermission()
				+ "," + Tables.User.Fields.CONTACT + "='" + user.getContact()
				+ "'," + Tables.User.Fields.REMARK + "='" + user.getRemark()
				+ "'  where " + Tables.User.Fields.USERID + "="
				+ user.getUserId();
		System.out.println("sql-->"+sql);
		DBUtil util = new DBUtil();
		Connection conn = util.openConnection();
		PreparedStatement prep;
		try {
			prep = conn.prepareStatement(sql);
			prep.execute();
			return true;
		} catch (SQLException e) {
			System.out.println("sql异常！");
			System.out.println("sql userRegister-->" + sql);
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * 检查账户是否存在
	 * 
	 * @param account
	 * @return
	 */
	public boolean checkAccountIsExists(String account) {
		String sql = "select count(*) result from " + Tables.User.TABLENAME
				+ " where " + Tables.User.Fields.ACCOUNT + " =? ";
		DBUtil util = new DBUtil();
		Connection conn = util.openConnection();

		try {
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setString(1, account);
			ResultSet rs = statement.executeQuery();
			if (rs.next()) {
				int result = rs.getInt("result");
				if (result > 0)
					return true;
			}
		} catch (SQLException e) {
			System.out.println("sql异常！");
			System.out.println("sql login-->" + sql);
			e.printStackTrace();
		}
		return false;
	}
	/**
	 * 根据账户得到用户
	 */
	public User getUserByAccount(String account) {
		User user = null;
		String sql = "select * from " + Tables.User.TABLENAME + " where "
				+ Tables.User.Fields.ACCOUNT + " = ?";
		System.out.println("sql-->"+sql);
		
		DBUtil util = new DBUtil();
		Connection conn = util.openConnection();
		try {
			PreparedStatement prep = conn.prepareStatement(sql);
			prep.setString(1, account);
			ResultSet rs = prep.executeQuery();
			if (rs.next()) {
				user = getUserFromResultSet(rs);
			}
		} catch (SQLException e) {
			System.out.println("sql异常！");
			System.out.println("sql getOrderByTableNum-->" + sql);
			e.printStackTrace();
		}
		return user;
	}

	/**
	 * 把ResultSet中的数据解析成User
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private User getUserFromResultSet(ResultSet rs) throws SQLException {
		User user = new User();
		user.setUserId(rs.getInt(Tables.User.Fields.USERID));
		user.setAccount(rs.getString(Tables.User.Fields.ACCOUNT));
		user.setPassword(rs.getString(Tables.User.Fields.PASSWORD));
		user.setName(rs.getString(Tables.User.Fields.NAME));
		user.setGender(rs.getInt(Tables.User.Fields.GENDER));
		user.setPermission(rs.getInt(Tables.User.Fields.PERMISSION));
		user.setContact(rs.getString(Tables.User.Fields.CONTACT));
		user.setRemark(rs.getString(Tables.User.Fields.REMARK));
		return user;
	}

	public boolean deleteUserById(int userId) {
		// TODO Auto-generated method stub
//		Boolean flag = false;
		String sql = "delete from " + Tables.User.TABLENAME + " where "
				+ Tables.User.Fields.USERID + " = "+userId;
		DBUtil util = new DBUtil();
		System.out.println("sql-->"+sql);
		Connection conn = util.openConnection();
		PreparedStatement prep;
		try {
			prep = conn.prepareStatement(sql);
			prep.executeUpdate();
			return true;
		} catch (SQLException e) {
			System.out.println("sql异常！");
			System.out.println("sql getOrderByTableNum-->" + sql);
			e.printStackTrace();
		}
		return false;
	}

}

 

第五步，建立对应的servlet类

package com.mjm.servlet;


import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mjm.bean.User;
import com.mjm.dao.UserDao;
import com.mjm.dao.impl.UserDaoImpl;
import com.mjm.tool.JsonToObject;
import com.mjm.tool.ToJsonString;

//

public class UserServlet extends HttpServlet {

	/**
	 * Constructor of the object.
	 */
	public UserServlet() {
		super();
	}

	/**
	 * Destruction of the servlet. <br>
	 */
	public void destroy() {
		super.destroy(); // Just puts "destroy" string in log
		// Put your code here
	}

	/**
	 * The doGet method of the servlet. <br>
	 * 
	 * This method is called when a form has its tag value method equals to get.
	 * 
	 * @param request
	 *            the request send by the client to the server
	 * @param response
	 *            the response send by the server to the client
	 * @throws ServletException
	 *             if an error occurred
	 * @throws IOException
	 *             if an error occurred
	 */
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		response.setContentType("text/plain");
		response.setCharacterEncoding("UTF-8");
		String json = null;
		UserDao dao = new UserDaoImpl();
		PrintWriter out = response.getWriter();
		String type = request.getParameter("type");
		System.out.println("type-->" + type);
		if (type.equals("login")) {
			String account = request.getParameter("account");
			String password = request.getParameter("password");
			System.out.println("account-->" + account);
			System.out.println("password-->" + password);
			if (dao.login(account, password))
				json = "1";
			else
				json = "0";
		}
		if (type.equals("userRegister")) {
			String userJson = request.getParameter("userJson");
			User user = JsonToObject.getUserFromJson(userJson);
			if (dao.checkAccountIsExists(user.getAccount())) {
				json = "2";
			} else {
				if (dao.userRegister(user))
					json = "1";
				else
					json = "0";
			}

		}
		if (type.equals("getAllUser")) {
			json = ToJsonString.listToJson(dao.getAllUser());
		}
		if (type.equals("updateUser")) {
			byte[] userByte = request.getParameter("userJson").getBytes(
					"ISO-8859-1");
			String userJson = new String(userByte, "UTF-8");
			User user = JsonToObject.getUserFromJson(userJson);
			System.out.println("user-->" + user);
			if (dao.updateUser(user))
				json = "1";
			else
				json = "0";
		}
		if (type.equals("deleteUserById")) {
			
			String sUserId = request.getParameter("userId");
			int userId=Integer.parseInt(sUserId);
			
			
//			byte[] userByte = request.getParameter("userJson").getBytes(
//					"ISO-8859-1");
//			String userJson = new String(userByte, "UTF-8");
//			User user = JsonToObject.getUserFromJson(userJson);
//			System.out.println("user-->" + user);
//			if (dao.deleteUserById(userId))
			
			
			
			
			if (dao.deleteUserById(userId))
				json = "1";
			else
				json = "0";
		}
		if (type.equals("getUserByAccount")) {
			String account = request.getParameter("account");
			System.out.println("account-->"+account);
			User user = dao.getUserByAccount(account);
			json = ToJsonString.objectToJson(user);
		}
		System.out.println("json-->" + json);
		out.println(json);
		out.flush();
		out.close();

	}

	/**
	 * The doPost method of the servlet. <br>
	 * 
	 * This method is called when a form has its tag value method equals to
	 * post.
	 * 
	 * @param request
	 *            the request send by the client to the server
	 * @param response
	 *            the response send by the server to the client
	 * @throws ServletException
	 *             if an error occurred
	 * @throws IOException
	 *             if an error occurred
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		doGet(request, response);
	}

	/**
	 * Initialization of the servlet. <br>
	 * 
	 * @throws ServletException
	 *             if an error occurs
	 */
	public void init() throws ServletException {
		// Put your code here
	}

}