继上一篇SpringMVC实现登录注册-1,上一篇是用list代替数据库,这一篇采用MySQL,采用c3p0连接数据库,jsp界面没变
数据库
新增两个jar包
工程项目
UserController.java
package com.zjq.controller;
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 org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import com.zjq.db.DbPool;
import com.zjq.pojo.*;
@RequestMapping(value="/user")
@Controller
public class UserController {
static Connection conn = DbPool.getConnectionFromPool();
static PreparedStatement pstmt = null;
private static ResultSet rs = null;
User user = new User();
/*注册界面*/
@RequestMapping(value="/showRegUser")
public String showRegUser() {
return "regUser";
}
/*新增用户*/
@RequestMapping(value="/addUser")
public String addUser(
@RequestParam("userName")String userName,
@RequestParam("password")String password,
@RequestParam("fullName")String fullName) throws Exception {
user.setUserName(userName);
user.setPassword(password);
user.setFullName(fullName);
if(savedata(user)) {
return "login";
}else {
return "regUser";
}
}
@RequestMapping(value="showLogin")
public String showLogin() { //直接跳转登录界面
return "login";
}
/*验证用户*/
@RequestMapping(value="/validateUser")
public String login(
@RequestParam("userName")String userName,
@RequestParam("password")String password,
Model model) {
int i;
try {
user.setUserName(userName);
i = search(userName,password);
if(i == 1) {
model.addAttribute("user",user); //回显数据
return "welcome";
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return "login";//验证失败,跳转到登录界面
}
/*判断密码是否正确,用户名是否存在*/
public int search(String name,String pass) throws SQLException
{
String sql="select * from user where username = ?";
int i = 1;
pstmt = conn.prepareStatement(sql);
pstmt.setString(i++, user.getUserName());
rs = pstmt.executeQuery();
if (rs == null) {
return 0;
}
if(rs.next()) {
String pas = rs.getString("password"); //判断密码是否正确
if(pas.equals(pass)) {
return 1;
}else {
return -1;
}
}
return 0;
}
/*注册的数据库操作*/
public static boolean savedata(User user) throws Exception {
String insertsql = "insert into user(username,password) values(?,?)";// 预处理的语句对象
int i = 1;
pstmt = conn.prepareStatement(insertsql);
pstmt.setString(i++, user.getUserName());
pstmt.setString(i++, user.getPassword());
int val = pstmt.executeUpdate();
if (val > 0) {
return true;
}
return false;
}
}
DbPool.java
package com.zjq.db;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class DbPool {
static DataSource dataSource;
static {
//如果没有参数,默认加载c3p0参数文件:文件名称、位置是固定的
dataSource=new ComboPooledDataSource();
}
public static Connection getConnectionFromPool(){
try {
Connection connection=dataSource.getConnection();
return connection;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
}
c3p0.properties
c3p0.jdbcUrl=jdbc:mysql://localhost:3306/你的数据库的名称
c3p0.driverClass=com.mysql.jdbc.Driver
c3p0.user=root
c3p0.password=这里写你的数据库的密码
c3p0.acquireIncrement=3
c3p0.idleConnectionTestPeriod=60
c3p0.initialPoolSize=10
c3p0.maxIdleTime=60
c3p0.maxPoolSize=20
c3p0.maxStatements=100
c3p0.minPoolSize=5