前言:
大概这篇文章应该应该已经没多少人能用到了。
一、本文用到的数据库连接:
先简单写一下Jdbc链接数据库(虽然现在这种方式应该是已经没人在用)
package com.jdbcdemo;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import org.junit.Test;
public class JdbcTest {
//1.普通的jdbc
@Test
public void test() throws SQLException {
Driver driver = new org.gjt.mm.mysql.Driver();
String url = "jdbc:mysql://localhost:3306/samp_db";
Properties info = new Properties();
info.put("user", "root");
info.put("password", "haibo1118");
Connection connection = driver.connect(url, info);
System.out.println(connection);
}
//2.配置文件property的jdbc
@Test
public void testProperty() throws Exception{
System.out.println(getConnection());
}
public Connection getConnection() throws Exception {
// TODO Auto-generated method stub
String driverClass;
String url;
String user;
String password;
InputStream in = getClass().getClassLoader().getResourceAsStream("jdbc.property");
Properties properties = new Properties();
properties.load(in);
driverClass = properties.getProperty("driver");
url = properties.getProperty("jdbcUrl");
user = properties.getProperty("user");
password = properties.getProperty("password");
Driver driver = (Driver) Class.forName(driverClass).newInstance();
Properties properties2 = new Properties();
properties2.put("user", user);
properties2.put("password", password);
Connection connection = driver.connect(url, properties2);
return connection;
}
//3.通过DriverManager获得链接,比之前简化一部分
@Test
public void testDriverManager() throws Exception{
System.out.println(getConnection2());
}
public Connection getConnection2() throws Exception{
String driverClass;
String url;
String user;
String password;
InputStream in = getClass().getClassLoader().getResourceAsStream("jdbc.property");
Properties properties = new Properties();
properties.load(in);
driverClass = properties.getProperty("driver");
url = properties.getProperty("jdbcUrl");
user = properties.getProperty("user");
password = properties.getProperty("password");
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
}
//4、通过自己的工具类简化建立链接,释放链接,并对数据库执行插入操作
@Test
public void testStateMent(){
Connection conn = null;
Statement statement = null;
String sql = "insert into customers (NAME,EMAIL,BIRTH) values('zhouhe','zhouhe@qq.com','1999-11-18');";
// String sql = "update customers set name = 'tom' where id =2;";
// String sql = "delete from customers where id=1;";
try {
conn = JdbcTools.getConnection();
statement = conn.createStatement();
statement.executeUpdate(sql);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcTools.release(conn, statement);
}
}
//5、通过自己的工具类简化建立链接,释放链接,并对数据库执行查询操作,获得结果集
@Test
public void testResultSet(){
Connection conn = null;
Statement statement = null;
ResultSet resultSet = null;
String sql = "SELECT * FROM samp_db.customers;";
try {
conn = JdbcTools.getConnection();
statement = conn.createStatement();
resultSet = statement.executeQuery(sql);
while(resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String email = resultSet.getString(3);
Date date = resultSet.getDate(4);
System.out.println(" id:"+id+" name:"+name+" email:"+email+" date:"+date);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcTools.release(resultSet, conn, statement);
}
}
}
一个自定义链接释放数据库链接的小工具:
package com.jdbcdemo;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JdbcTools {
//释放
public static void release(Connection connection,Statement statement){
if (statement!=null) {
try {
statement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//释放
public static void release(ResultSet resultSet,Connection connection,Statement statement){
if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (statement!=null) {
try {
statement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//获得链接
public static Connection getConnection() throws Exception{
String driverClass;
String url;
String user;
String password;
InputStream in = JdbcTools.class.getClassLoader().getResourceAsStream("jdbc.property");
Properties properties = new Properties();
properties.load(in);
driverClass = properties.getProperty("driver");
url = properties.getProperty("jdbcUrl");
user = properties.getProperty("user");
password = properties.getProperty("password");
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
}
}
配置文件jdbc.property:
driver=org.gjt.mm.mysql.Driver
jdbcUrl=jdbc:mysql://localhost:3306/samp_db
user=root
password=haibo1118
目录结构一览:
二、写一个登陆功能模块:
登陆也就是通过客户端提供用户名密码,去数据库查询是否有这一条数据,有则返回token。没有则做异常处理。
1、先写去数据库查询的代码:
package login;
import model.UserInfo;
public interface UserDao {
public UserInfo login(String username,String password);
}
实现UserDao接口:
package login;
import java.sql.Connection;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import jdbc.JdbcTools;
import model.UserInfo;
public class UserDaoImpl implements UserDao {
Connection conn;
UserInfo obj;
@Override
public UserInfo login(String username, String password) {
// TODO Auto-generated method stub
QueryRunner queryRunner = new QueryRunner();
String sql ="select username,password from userTbl where username=? and password=?";
Connection connection = null;
try {
connection = JdbcTools.getConnection();
obj = (UserInfo) queryRunner.query(connection, sql, new BeanHandler(UserInfo.class),username,password);
System.out.println(obj);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
JdbcTools.release(connection, null);
}
return obj;
}
}
本文通过一个第三方工具dbutils简化查询操作:org.apache.commons.dbutils.QueryRunner;本例子,如果查到用户名密码结果则返回一个对象,如果没找到则返回空对象。
此处为注释分割线---------可忽略此部分(下文)----------
PS1:此处提供一个较为完整的Jdbctools工具类:(包含事务,虽然本例并没有用到,可忽略)
package jdbc;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JdbcTools {
public static void beginTx(Connection conn){
try {
conn.setAutoCommit(false);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void commitTx(Connection conn){
try {
conn.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void rollback(Connection connection){
try {
connection.rollback();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void setTransactionLevel(Connection conn){
try {
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void update(String sql){
Connection connection = null;
Statement statement = null;
try {
connection = getConnection();
statement = connection.createStatement();
statement.execute(sql);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
release(connection, statement);
}
}
public static void release(Connection connection,Statement statement){
if (statement!=null) {
try {
statement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void release(ResultSet resultSet,Connection connection,Statement statement){
if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (statement!=null) {
try {
statement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static Connection getConnection() throws Exception{
String driverClass;
String url;
String user;
String password;
InputStream in = JdbcTools.class.getClassLoader().getResourceAsStream("jdbc.property");
Properties properties = new Properties();
properties.load(in);
driverClass = properties.getProperty("driver");
url = properties.getProperty("jdbcUrl");
user = properties.getProperty("user");
password = properties.getProperty("password");
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
}
public static void update(String sql,Object ...args){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql);
for(int i = 0 ;i<args.length;i++){
preparedStatement.setObject(i+1, args[i]);
}
preparedStatement.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
release(connection, preparedStatement);
}
}
}
PS2:建议在使用jdbc时先写一个测试用例试试是否成功连接数据库:
package jdbc;
import java.sql.Connection;
import org.junit.Test;
public class TestJdbc {
Connection conn;
@Test
public void test() {
try {
conn = JdbcTools.getConnection();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println(conn);
}
}
执行结果如下:则成功。
com.mysql.jdbc.JDBC4Connection@3cd1f1c8
此处为注释分割线---------可忽略此部分(上文)----------
2、写访问服务端代码:
package login;
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 javax.servlet.http.HttpSession;
import model.UserInfo;
/**
* Servlet implementation class MyLoginServlet
* @WebServlet("/MyLoginServlet")
*/
public class MyLoginServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public MyLoginServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
String username = request.getParameter("username");
String password = request.getParameter("password");
UserDao userDao = new UserDaoImpl();
UserInfo u = userDao.login(username, password);
PrintWriter out = response.getWriter();
if (u!=null) {
HttpSession session = request.getSession();
session.setAttribute("userSession", u);
request.getRequestDispatcher("welcome.jsp").forward(request, response);
}else{
out.print("username or password is incorrect!");
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
当前代码配置一下web.xml其实就可以直接执行了:
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
version="3.1">
<display-name>LoginServlet</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.htm</welcome-file>
<welcome-file>default.jsp</welcome-file>
</welcome-file-list>
<servlet>
<servlet-name>MyLoginServlet</servlet-name>
<servlet-class>login.MyLoginServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>MyLoginServlet</servlet-name>
<url-pattern>/MyLoginServlet</url-pattern>
</servlet-mapping>
</web-app>
执行执行地址如下:http://localhost:8080/LoginServlet/MyLoginServlet
浏览器将根据我们的代码显示:username or password is incorrect!因为我们未传入用户名密码。
3、写一个login.html网页提交用户名,密码:
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>hello servlet</h1>
<form action="MyLoginServlet" method="post">
Username:<input type="text" name="username">
Password:<input type="text" name="password">
<input type="submit" value="login">
</form>
</body>
</html>
由于我们查询成功
if (u!=null) {
HttpSession session = request.getSession();
session.setAttribute("userSession", u);
request.getRequestDispatcher("welcome.jsp").forward(request, response);
}else{
out.print("username or password is incorrect!");
}
会执行跳转welcome.jsp。4、那么就写一个成功后的欢迎页面welcome.jsp吧。
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
Welcome,${userSession.username}
</body>
</html>
测试一下:http://localhost:8080/LoginServlet/login.html
输入正确的用户名,密码,页面显示:Welcome,xionghaibo。
大功告成!下一步SpringMVC实现一遍。