在Servlet中使用JDBC访问数据库
UserInfo类
public class UserInfo {
private String id;
private String name;
private String pwd;
private String sex;
private int age;
private String email;
public UserInfo(){}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
}
LoginDao类
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import com.cn.zj.bean.UserInfo;
public class LoginDao {
private static LoginDao instance = null;
public static LoginDao getInstance(){
if(instance == null){
instance = new LoginDao();
}
return instance;
}
//保存用户注册信息
public boolean saveUser(UserInfo user){
String sql = "insert into tb_userinfo(name,pwd,sex,age,email) values(?,?,?,?,?)";//insert SQL语句
Connection conn = null;
try{
conn = MySQLDBCon.getConnection();//建立数据库连接
PreparedStatement pstmt = conn.prepareStatement(sql);//创建PreparedStatement对象
pstmt.setString(1, user.getName()); //为SQL语句第1个参数赋值;
pstmt.setString(2, user.getPwd()); //为SQL语句第2个参数赋值;
pstmt.setString(3, user.getSex()); //为SQL语句第3个参数赋值;
pstmt.setInt(4, user.getAge()); //为SQL语句第4个参数赋值;
pstmt.setString(5, user.getEmail()); //为SQL语句第5个参数赋值;
pstmt.executeUpdate(); //执行insert语句
return true;
}catch(Exception ex){
ex.printStackTrace();
}finally{
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
}
MySQLDBCon类
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class MySQLDBCon {
public static void main(String[] args) {
Connection conn=new MySQLDBCon().getConnection();
System.out.println(conn);
}
public static Connection getConnection() {
Connection conn = null;
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8";
String username = "root";
String password = "root";
String querySql="select * from tb_userinfo";
try {
Class.forName(driver);
conn = DriverManager.getConnection(url,username,password);
if(!conn.isClosed())
System.out.println("Succeeded connecting to the Database!");
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery(querySql);
while(rs.next()){
System.out.println("姓名:"+rs.getString("name")+"密码:"+rs.getInt("pwd")+"性别"+rs.getString("sex")+"年龄"+rs.getInt("age")+"Email"+rs.getString("email"));
}
}catch(ClassNotFoundException e) {
System.out.println("Sorry,can`t find the Driver!");
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}catch (Exception e){
e.printStackTrace();
}finally{
System.out.println("数据库数据成功获取!!");
}
return conn;
}
}
LoginServlet类
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.cn.zj.bean.UserInfo;
import com.cn.zj.dao.LoginDao;
public class LoginServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");//设置请求字符编码格式
/**以下是获得表单提交过来的值*/
String name = request.getParameter("name");
String pwd= request.getParameter("pwd");
String sex = request.getParameter("sex");
String age = request.getParameter("age");
int userAge =0;
if(age!=null&&!age.equals("")){
userAge = Integer.parseInt(age);
}
String email = request.getParameter("email");
/**以下代码将获得的表单值封装到用户信息对象中*/
UserInfo user = new UserInfo();
user.setName(name);
user.setPwd(pwd);
user.setSex(sex);
user.setAge(userAge);
user.setEmail(email);
boolean res = LoginDao.getInstance().saveUser(user);//将用户注册信息保存到数据库
response.setContentType("text/html");
response.setCharacterEncoding("UTF-8");
PrintWriter out = response.getWriter();
out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
out.println("<HTML>");
out.println(" <HEAD><TITLE>JDBC访问数据库</TITLE></HEAD>");
out.println(" <BODY>");
if(res){
out.println("<font color='green' size=2>注册成功!</font>");
}else{
out.println("<font color='orangered' size=2>注册失败!</font>");
}
out.println(" </BODY>");
out.println("</HTML>");
out.flush();
out.close();
}
/**
* Initialization of the servlet. <br>
*
* @throws ServletException if an error occurs
*/
public void init() throws ServletException {
// Put your code here
}
}
index.jsp文件
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>用户注册</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
<style type="text/css">
table{
font-size:12px;
font-family: 隶书;
color:gray;
border: 1px green solid;
}
input{
font-size:12px;
font-family: 隶书;
color:gray;
}
</style>
</head>
<body>
<form action="login" method="post">
<table align="center">
<tr>
<td>用户名:</td>
<td><input type="text" name="name" /></td>
</tr>
<tr>
<td>密码:</td>
<td><input type="password" name="pwd" /></td>
</tr>
<tr>
<td>性别:</td>
<td>
<input type="radio" name="sex" value="男" />男
<input type="radio" name="sex" value="女" />女
</td>
</tr>
<tr>
<td>年龄:</td>
<td><input type="text" name="age" /></td>
</tr>
<tr>
<td>Email:</td>
<td><input type="text" name="email" /></td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="注 册" />
<input type="reset" value="重 置" />
</td>
</tr>
</table>
</form>
</body>
</html>
web.xml文件配置
<servlet>
<description>This is the description of my J2EE component</description>
<display-name>This is the display name of my J2EE component</display-name>
<servlet-name>LoginServlet</servlet-name>
<servlet-class>com.cn.zj.servlet.LoginServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>LoginServlet</servlet-name>
<url-pattern>/login</url-pattern>
</servlet-mapping>
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
<resource-ref>
<description>conn</description>
<res-ref-name>jdbc/mysql</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>