第一步先把数据库连接通
有两种方式可以用
第一在代码中实现
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
}
}