1.创建一个动态WEB工程
拷入相应的jar包(注意:jar包的位置在lib目录下面)
导入数据库homework1.sql
Create database homework1;
Use homework1;
Create table user (
id int(4) auto_increment primary key,
Username varchar(30) not null unique,
Age varchar(10),
Sex varchar(10),
Hobby varchar(30),
Password varchar(30)
);
Insert into user(Username ,age,sex,hobby,password) values(‘hello’,20,’男’,’打篮球’,’123456’);
2.在src拷入C3P0配置文件
<?xml version='1.0' encoding='UTF-8'?>
<c3p0-config>
<default-config> <!--默认配置方式-->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///homework1</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
</c3p0-config>
3.创建连接池
package com.wangrui.c3p0pool;
import java.sql.Connection;
import java.sql.SQLException;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class JDBCC3P0Pool {
private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
public static Connection getConnect() {
Connection conn = null;
try {
conn = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
}
4.测试连接情况
package com.wangrui.c3p0test;
/**
* 该类用于查询数据库连接是否正确
* @author wangrui
*
*/
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.wangrui.c3p0pool.JDBCC3P0Pool;
public class C3P0Test {
public static void main(String[] args) {
Connection conn = JDBCC3P0Pool.getConnect();
System.out.println(conn);
try {
PreparedStatement ps = conn.prepareStatement("select *from user");
ResultSet query = ps.executeQuery();
while(query.next()) {
System.out.println(query.getString("username")
+"--"+query.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
5 写登录注册界面(注意写在WebContent目录下)
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>登录</title>
<style>
body{
text-align:center
}
.div{
margin:0 auto;
width:400px;
height:300px;
border:1px solid #F00
}
.div input{
margin:10px;
}
</style>
</head>
<body>
<div class="div">
<h1>登录</h1>
<form action="login" method="post">
<label>用户名</label>
<input type="text" name="username"/>
<br>
<label>密码</label>
<input type="password" name="password"/>
<br>
<input type="submit" value="提交">
<br>
<label>没有账号?<a href="regist.html">立马注册</a></label>
</form>
</div>
</body>
</html>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>注册</title>
<style>
body{
text-align:center
}
.div{
margin:0 auto;
width:400px;
height:400px;
border:1px solid #F00
}
.div input{
margin:10px;
}
</style>
</head>
<body>
<div class="div">
<h1>注册</h1>
<form action="register" method="post">
<label>姓名(用户名)*</label>
<input type="text" name="username"/>
<br>
<label>年龄</label>
<input type="text" name="age"/>
<br>
<label>性别</label>
<input type="text" name="sex"/>
<br>
<label>爱好</label>
<input type="text" name="hobby"/>
<br>
<label>密码*</label>
<input type="password" name="password"/>
<br>
<input type="submit" value="注册">
</form>
</div>
</body>
</html>
6,.写登录、注册Servlet
package com.wangrui.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.wangrui.controller.login;
public class LoginServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
private login login;
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//1.设置请求、响应编码
req.setCharacterEncoding("utf-8");
resp.setContentType("text/html;charset=utf-8");
//2.从表单获取数据
String username = req.getParameter("username");
String password = req.getParameter("password");
//3.数据库查询数据并返回结果
login login= new login();
boolean userlogin = login.userlogin(username, password);
if(userlogin) {
resp.getWriter().write("<h1>恭喜你!!!登录成功</h1>");
}else {
System.out.println(userlogin+"登录失败!!!用户名或密码错误");
resp.getWriter().write("<h1>登录失败!!!用户名或密码错误</h1>");
}
}
}
package com.wangrui.controller;
/**
* 该类用于查询用户登录的信息是否正确
* @author wangrui
*
*/
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.wangrui.c3p0pool.JDBCC3P0Pool;
public class login {
public boolean userlogin(String username,String password){
if(username.equals("") || password.equals("")){
return false;
}
Connection conn=JDBCC3P0Pool.getConnect();
PreparedStatement ps;
try{
String sql="SELECT * FROM user where username = '"+username+
"' and password = '"+password+"'";
ps = conn.prepareStatement(sql);
ResultSet query = ps.executeQuery();
return query.next();
}catch (Exception e) {
e.printStackTrace();
System.out.println(e);
}
return false;
}
}
package com.wangrui.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.wangrui.controller.register;
public class RegisterServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//1.设置请求、响应编码
req.setCharacterEncoding("utf-8");
resp.setContentType("text/html;charset=utf-8");
//2.从表单获取数据
String username = req.getParameter("username");
String password = req.getParameter("password");
String s = req.getParameter("age");
int age = Integer.parseInt(s);
String sex = req.getParameter("sex");
String hobby = req.getParameter("hobby");
//3.数据库查询数据并返回结果
register register =new register();
boolean userRegister = register.userRegister(username, age, sex, hobby, password);
if(userRegister) {
System.out.println(userRegister+"注册成功");
resp.getWriter().write("<h1>恭喜你!!!注册成功<a href=\"login.html\">马上登录</a></h1>");
}else {
System.out.println(userRegister+"注册失败!!!");
resp.getWriter().write("<h1>注册失败!!!</h1>");
}
}
}
package com.wangrui.controller;
import java.sql.Connection;
import java.sql.PreparedStatement;
import com.wangrui.c3p0pool.JDBCC3P0Pool;
/**
* 该类用于查询用户注册的信息是否正确
* @author wangrui
*
*/
public class register {
public boolean userRegister(String username,int age,String sex,String hobby,String password){
if(username.equals("") || password.equals("")){
return false;
}
Connection conn=JDBCC3P0Pool.getConnect();
PreparedStatement ps;
try{
String sql=" insert into user(username,age,sex,hobby,password)"
+ " values('"+username+"',"+age+",'"+sex+"','"+hobby+"','"+password+"')";
ps = conn.prepareStatement(sql);
int num = ps.executeUpdate();//添加、删除、更新数据
if(num>0){
System.out.println("插入数据成功");
}
return true;
}catch (Exception e) {
e.printStackTrace();
System.out.println(e);
}
return false;
}
}