-
环境准备
1.创建表create database newdb3 character set utf8; use newdb3; create table user2( id int primary key auto_increment, username varchar(20), password varchar(20) )charset=utf8;
- 数据库链接文件
driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/newdb3?useUnicode=true&characterEncoding=UTF-8 username=root password=
DBUtils.java
package cn.tedu.servlet; import org.apache.commons.dbcp.BasicDataSource; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.SQLException; import java.util.Properties; public class DBUtils { //支持数据库连接池和jdbc.properties的DBUtils类 //声明一个数据连接池对象 private static BasicDataSource ds; //静态块中，初始化数据连接对象 static { //获得这个文件的输入流 InputStream is = DBUtils.class.getClassLoader() .getResourceAsStream("jdbc.properties"); //实例化Properties类用于解析jdbc.properties Properties p = new Properties(); //加载jdbc.properties文件中获得连接数据库需要的参数变量 try { p.load(is); } catch (IOException e) { e.printStackTrace(); } //从jdbc.properties文件中获得连接数据库需要的参数变量 String driver = p.getProperty("driver"); String url = p.getProperty("url"); String username = p.getProperty("username"); String password = p.getProperty("password"); //将获取的信息赋值给数据连接池作为参数 //赋值前先进行实例化对象，不然后报空指针错误 ds = new BasicDataSource(); ds.setDriverClassName(driver); ds.setUrl(url); ds.setUsername(username); ds.setPassword(password); //不设置也有默认值 ds.setInitialSize(3); ds.setMaxActive(5); //最大空闲数 ds.setMaxIdle(3); } //获得连接结构没有变化 public static Connection getConn() throws SQLException { //通过连接池获取连接对象 Connection conn = ds.getConnection(); // System.out.println(conn); return conn; } } -
注册、登录、修改密码功能
- 注册
public class RegServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
String username = request.getParameter("username");
String password = request.getParameter("password");
//使用jdbc注册用户
System.out.println(username+":"+password);
try(Connection conn = DBUtils.getConn()){
//定义sql语句
String sql="insert into user2 values(null,?,?)";
PreparedStatement st = conn.prepareStatement(sql);
//为?赋值
st.setString(1,username);
st.setString(2,password);
//执行sql
int num = st.executeUpdate();
if (num>0){
System.out.println("注册成功");
//防止中文乱码
response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter();
out.print("注册成功");
out.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
- 登录
public class LoginServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
String username = request.getParameter("username");
String password = request.getParameter("password");
System.out.println(username+":"+password);
try(Connection conn = DBUtils.getConn()){
String sql = "select count(*) from user2 where username=? and password=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,username);
ps.setString(2,password);
//执行查询，返回结果集
ResultSet rs = ps.executeQuery();
//防止乱码
response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter();
while (rs.next()){
//获得查询出的人数
int count = rs.getInt(1);
if (count>0){
System.out.println("console:登录成功");
out.print("登录成功");
}else {
System.out.println("console:登录失败");
out.print("登录失败");
}
}
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
}
删除
public class UpdateServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
String username = request.getParameter("username");
String newpwd = request.getParameter("newpwd");
String oldpwd = request.getParameter("oldpwd");
System.out.println(username+","+newpwd+","+oldpwd);
try(Connection conn = DBUtils.getConn()){
String sql = "update user2 set password=? where username=? and password=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,newpwd);
ps.setString(2,username);
ps.setString(3,oldpwd);
int num = ps.executeUpdate();
System.out.println(num);
if (num>0){
System.out.println("修改成功");
//防止中文乱码
response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter();
out.print("修改成功");
out.close();
}else{
System.out.println("修改失败");
//防止中文乱码
response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter();
out.print("修改失败");
out.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
